[sql] 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).

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

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

select concat('a','b') 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

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's also concat, but it doesn't get used much

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

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's also concat, but it doesn't get used much

select concat('a','b') 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