Meaning of `||` in an SQL SELECT statement

Question

I am modifying some SQL code and I have come across the statement:

SELECT 'pilot' || cast(id as string) AS id from ....

What does || do in this statement?

I thought it would replace falsey values by 'pilot'; but that is not the case, it returns NULL values. There weren't any empty strings, but there were no rows with 'pilot' either.

Does this answer your question? [What does double bars (||) mean in SQL?](https://stackoverflow.com/questions/23919378/what-does-double-bars-mean-in-sql) — D M, May 27 '21 at 21:44
It is the SQL standard operator for string concatenation. It should be ignoring `NULL` values -- but perhaps not all databases do that. — Gordon Linoff, May 27 '21 at 21:44

score 3 · Accepted Answer · answered May 27 '21 at 21:57

|| is the string concatenation operator. You can't concatenate null to a string.

If you have a table tbl...

id	col1	col2
1	a1	a2
(null)	b1	b2

...and you ask...

select col1
, 'pilot' || cast(id as string) AS id
from tbl

...you'll get...

col1	id
a1	pilot1
b1

...because...

'pilot' || null

...returns null.

(You should specify which RDBMS you are using.) This may not be true in Oracle because it treats NULL and '' as equivalent. But it appears likely that id is an int field, so you may get the same results because of the cast.

Meaning of `||` in an SQL SELECT statement

1 Answers1