What does SQL Select symbol || mean?

Question

What does || do in SQL?

SELECT 'a' || ',' || 'b' AS letter

See also: http://stackoverflow.com/questions/1373238/string-concatenation-operator-in-oracle-postgres-and-sql-server — SQLMason, Apr 29 '14 at 18:48
It doesn't add anything in your example, but is helpful in other contexts, e.g. in `... WHERE Name LIKE '%' || :searched || '%'...` — mins, May 24 '21 at 13:04

score 94 · Answer 1 · edited May 23 '17 at 12:34

94

|| represents string concatenation. Unfortunately, string concatenation is not completely portable across all sql dialects:

ansi sql: || (infix operator)
mysql: concat ( vararg function ). caution: || means 'logical or' (It's configurable, however; thanks to @hvd for pointing that out)
oracle: || (infix operator), concat ( caution: function of arity 2 only ! )
postgres: || (infix operator)
sql server: + (infix operator), concat ( vararg function )
sqlite: || (infix operator)

hopefully the confusion is complete ...

edited May 23 '17 at 12:34

Community

1
1

answered Apr 29 '14 at 18:34

collapsar

17,010
4
35
61

4

[It's configurable in MySQL.](http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_pipes_as_concat) – Apr 29 '14 at 19:14
4

so MySQL is the only RDBMS where '||' is equivalent to logical OR? – Andrey M. Stepanov Nov 10 '18 at 21:41
Also [MariaDB](https://mariadb.com/kb/en/or/) treats `||` as logical OR by default, because MariaDB is a fork of MySQL. They have both changed enough since the fork that they should be considered different products, but they still share this behavior. – Bill Karwin Nov 01 '22 at 17:07

score 9 · Answer 2 · answered Jun 28 '18 at 06:04

9

SELECT 'a' || ',' || 'b' AS letter will combine a letter. The result become 'a,b'

answered Jun 28 '18 at 06:04

ivanprakasa

207
1
4
11

the `||` operator concatenates strings – aydow Jun 28 '18 at 06:27

score 6 · Answer 3 · edited May 23 '17 at 11:55

6

It is a concat statement. It will concatenate the two strings.

Here is a helpful post!

What is the difference between "||" operator and concat function in Oracle?

edited May 23 '17 at 11:55

Community

1
1

answered Apr 29 '14 at 18:32

John Hartsock

85,422
23
131
146

Andrew · Answer 4 · 2014-04-29T19:28:33.927

5

It's a concatenation operator. So you would get 'a,b' from that. I think || will work on most RDBMS's. SQL Server requires the + operator (thanks to HVD for setting me straight!).

edited Apr 29 '14 at 19:28

answered Apr 29 '14 at 18:32

Andrew

8,445
3
28
46

1

Microsoft SQL Server is one of the exceptions: it doesn't support `||`, and requires `+`. – Apr 29 '14 at 19:13

SQLMason · Answer 5 · 2014-04-29T18:42:05.807

5

In Oracle, SQLite3, and MySQL, it concatenates strings. Please see the Oracle documentation. The MySQL documentation.

Also, it's part of ANSI SQL, but read this for more information.

edited Apr 29 '14 at 18:42

answered Apr 29 '14 at 18:32

SQLMason

3,275
1
30
40

score 1 · Answer 6 · answered Apr 29 '14 at 18:33

1

in oracle its a shortcut for concatenate

http://docs.oracle.com/cd/B19306_01/server.102/b14200/operators003.htm

answered Apr 29 '14 at 18:33

Jonathan

1,542
3
16
24

What does SQL Select symbol || mean?

6 Answers6

Linked

Related