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

sqloracleplsqlstring-concatenation


Solution 1:[1]

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

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

Solution 2:[2]

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

Solution 3:[3]

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

Solution 4:[4]

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

Solution 5:[5]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gary Myers
Solution 2 Community
Solution 3 Community
Solution 4 Grant Shannon
Solution 5 Du-Lacoste

Related Questions