200

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

mahi_0707
  • 1,030
  • 9
  • 17
AJ.
  • 13,461
  • 19
  • 51
  • 63

6 Answers6

283

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.

Vidar S. Ramdal
  • 1,164
  • 1
  • 14
  • 38
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 3
    I'd expect null from a logical operation... not sure I'd ever thought about a string operation. –  Nov 10 '08 at 15:48
  • 1
    Well of course Oracle treats null and '' as the same, and 'x' || '' = 'x' makes sense. But if you think of null as "undefined" or "unknown" then 'x' || null could be any string beginning with 'x' and so is itself "unknown"! – Tony Andrews Nov 10 '08 at 16:02
  • 5
    `||` in Oracle is not a logical operator, therefore, `'x'||null` returns `x`. – Iľja Jun 27 '12 at 11:52
  • 4
    @ipip: I am confused - if by "logical operator" you mean operators like `AND`, `NOT` etc. then of course `||` is not a logical operator. But what does that have to due with `'x'||null` returning `x`? `n+null` returns null, so is `+` a logical operator? – Tony Andrews Jul 20 '12 at 15:36
  • 2
    Oracle's handling of null in concatenation is non-standard in that it is different to the SQL92 spec (and Postgres) - see http://www.postgresql.org/message-id/921.1144705646@sss.pgh.pa.us – beldaz Nov 10 '13 at 02:53
70

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

select concat('a','b') from dual;
Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • 6
    this is way better than the || symbol. using || is just confusing as per other language's use of ||. – jordan Apr 18 '13 at 15:28
  • I prefer `concat()` to `||` for clarity. – afaulconbridge Jul 22 '13 at 14:21
  • This is what I needed. Wanted to use it in expression AND REGEXP_LIKE(CONCAT( – Yasen Jul 23 '13 at 12:23
  • 18
    Agreed for clarity, but || has the advantage to allow more then 2 fields easily – iDevlop Jan 16 '14 at 09:42
  • 3
    `CONCAT` is also compatible with other DBMSes (at least MySQL and Postgres). – lapo Oct 13 '15 at 15:07
  • 1
    Odd that it didn't occur to the ANSI SQL committee that anyone might need to concatenate more than two things. (Same goes for the geniuses at Oracle who came up with `nvl()`.) – William Robertson Nov 15 '15 at 11:44
  • 1
    `CONCAT` is also [available in Microsoft SQL Server 2012 and onwards](https://msdn.microsoft.com/en-us/library/hh231515.aspx). CONCAT, though nonstandard, is definitely the way to go if you want your code to be portable. (`||` is the actual ANSI standard operator, though you wouldn't know it by looking at the support for it!) – Matt Gibson Mar 31 '16 at 12:57
  • Unfortunatly CONCAT() accepts only two arguments. You can concatenate indefinite numbre of strings using || – abrittaf Dec 26 '16 at 19:56
18

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
Community
  • 1
  • 1
Fabio Fantoni
  • 3,077
  • 3
  • 22
  • 32
7
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

Community
  • 1
  • 1
Ankur
  • 85
  • 1
  • 1
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;
Du-Lacoste
  • 11,530
  • 2
  • 71
  • 51
3

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
Grant Shannon
  • 4,709
  • 1
  • 46
  • 36