What is the string concatenation operator in Oracle?
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).
||, for example:
select 'Mr ' || ename from emp;
The only "interesting" feature I can think of is that
'x' || null returns
null as you might perhaps expect.
Read more... Read less...
There's also concat, but it doesn't get used much
select concat('a','b') from dual;
I would suggest concat when dealing with 2 strings, and || when those strings are more than 2:
select concat(a,b) from dual
select 'a'||'b'||'c'||'d' from dual
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
CONCAT(CONCAT(,),) worked for me when concatenating more than two strings.
My problem required working with date strings (only) and creating
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