75

What does || do in SQL?

SELECT 'a' || ',' || 'b' AS letter
3
  • See also: stackoverflow.com/questions/1373238/…
    – SQLMason
    Apr 29, 2014 at 18:48
  • 5
    Did you try executing it?
    – Martin Smith
    Apr 29, 2014 at 18:49
  • It doesn't add anything in your example, but is helpful in other contexts, e.g. in ... WHERE Name LIKE '%' || :searched || '%'...
    – mins
    May 24, 2021 at 13:04

6 Answers 6

Reset to default
97

|| 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 ...

3
  • 5
    It's configurable in MySQL.
    – user743382
    Apr 29, 2014 at 19:14
  • 4
    so MySQL is the only RDBMS where '||' is equivalent to logical OR?
    – Andrey M. Stepanov
    Nov 10, 2018 at 21:41
  • Also MariaDB 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 1, 2022 at 17:07
9

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

1
  • 1
    the || operator concatenates strings
    – aydow
    Jun 28, 2018 at 6:27
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?

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.

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!).

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

in oracle its a shortcut for concatenate

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.