What is the string concatenation operator in Oracle?
SqlOraclePlsqlString ConcatenationSql Problem Overview
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).
Sql Solutions
Solution 1 - Sql
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 - Sql
There's also concat, but it doesn't get used much
select concat('a','b') from dual;
Solution 3 - Sql
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 - Sql
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 - Sql
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 6 - Sql
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;