What is the string concatenation operator in Oracle SQL?

Are there any "interesting" features I should be careful of?

(This seems obvious, but I couldn't find a previous question asking it).

1

6 Answers

It is ||, for example:

select 'Mr ' || ename from emp;

The only "interesting" feature I can think of is that 'x' || null returns 'x', not null as you might perhaps expect.

5

There's also concat, but it doesn't get used much

select concat('a','b') from dual;
8

I would suggest concat when dealing with 2 strings, and || when those strings are more than 2:

select concat(a,b) from dual

or

 select 'a'||'b'||'c'||'d' from dual
2
DECLARE a VARCHAR2(30); b VARCHAR2(30); c VARCHAR2(30); BEGIN a := ' Abc '; b := ' def '; c := a || b; DBMS_OUTPUT.PUT_LINE(c); END;

output:: Abc def

Using CONCAT(CONCAT(,),) worked for me when concatenating more than two strings.

My problem required working with date strings (only) and creating YYYYMMDD from YYYY-MM-DD as follows (i.e. without converting to date format):

CONCAT(CONCAT(SUBSTR(DATECOL,1,4),SUBSTR(DATECOL,6,2)),SUBSTR(DATECOL,9,2)) AS YYYYMMDD

There are two ways to concatenate Strings in Oracle SQL. Either using CONCAT function or || operator.

CONCAT function allows you to concatenate two strings together

SELECT CONCAT( string1, string2 ) FROM dual;

Since CONCAT function will only allow you to concatenate two values together. If you want to concatenate more values than two, you can nest multiple CONCAT function calls.

SELECT CONCAT(CONCAT('A', 'B'),'C') FROM dual;

An alternative to using the CONCAT function would be to use the || operator

SELECT 'My Name' || 'My Age' FROM dual;