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).
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.
There's also concat, but it doesn't get used much
select concat('a','b') from dual;
CONCAT
is also compatible with other DBMSes (at least MySQL and Postgres).
nvl()
.)
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!)
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
concat(a,b)
over a||b
?
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 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;
Success story sharing
||
in Oracle is not a logical operator, therefore,'x'||null
returnsx
.AND
,NOT
etc. then of course||
is not a logical operator. But what does that have to due with'x'||null
returningx
?n+null
returns null, so is+
a logical operator?