What is the string concatenation operator in Oracle?

Question

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

Solution 1

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.

Solution 2

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

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

Solution 3

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 4

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 5

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

View more solutions

Comments

[Handsome Nerd]

docs.oracle.com/cd/B19306_01/server.102/b14200/operators003.‌​htm

[Admin]

I'd expect null from a logical operation... not sure I'd ever thought about a string operation.

[Tony Andrews]

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"!

[Iľja]

|| in Oracle is not a logical operator, therefore, 'x'||null returns x.

[Tony Andrews]

@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?

[jordan]

this is way better than the || symbol. using || is just confusing as per other language's use of ||.

[afaulconbridge]

I prefer concat() to || for clarity.

[Yasen]

This is what I needed. Wanted to use it in expression AND REGEXP_LIKE(CONCAT(

[beldaz]

Oracle's handling of null in concatenation is non-standard in that it is different to the SQL92 spec (and Postgres) - see postgresql.org/message-id/[email protected]

[lanartri]

Agreed for clarity, but || has the advantage to allow more then 2 fields easily

[lapo]

CONCAT is also compatible with other DBMSes (at least MySQL and Postgres).

[William Robertson]

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

[Matt Gibson]

CONCAT is also available in Microsoft SQL Server 2012 and onwards. 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!)

[abrittaf]

Unfortunatly CONCAT() accepts only two arguments. You can concatenate indefinite numbre of strings using ||

[Tony Andrews]

sorry I realise this was 2 years ago, but why would you prefer concat(a,b) over a||b?

[JoshYates1980]

|| shorter, more flexible, and simple. Look at his select statement.