String concatenation in oracle

String concatenation is joining one or more string to build a new string. Oracle use [cci]||[/cci] to join two string.

[sourcecode lang=”sql”]
SELECT ‘semur’ || ‘jengkol’ FROM dual
[/sourcecode]

Above statement will result string [cci]semurjengkol[/cci]. Note, you must use single quote [cci]'[/cci] to represent a string in oracle or error will be shown.

Adding prefix to column result

String concatenation can be used to giving a prefix (or suffix) to column result. For example you have [cci]person[/cci] table contains 2 columns, [cci]ID[/cci] and [cci]NAME[/cci].

[sourcecode lang=”plain”]
ID NAME
1 Ahmad
2 Fatih
3 Umar
[/sourcecode]

The statement below will give prefix “Mr.” to column [cci]NAME[/cci] int the result

[sourcecode lang=”sql”]
SELECT ID, ‘Mr. ‘ || NAME FROM person;
[/sourcecode]

Here the result:

[sourcecode lang=”plain”]
ID NAME
1 Mr. Ahmad
2 Mr. Fatih
3 Mr. Umar
[/sourcecode]

Joining more than two strings

As you can guest, joining more than two strings is trivial. Just separates all strings with [cci]||[/cci] operator.

[sourcecode lang=”sql”]
SELECT ID, "Mr. " || NAME || ‘, you are great’ FROM person;
[/sourcecode]

And the result is:

[sourcecode lang=”plain”]
ID NAME
1 Mr. Ahmad, you are great
2 Mr. Fatih, you are great
3 Mr. Umar, you are great
[/sourcecode]

Joining strings which contains quotes (‘ or “)

To join string which contains single quote ([cci]'[/cci]) or double quote ([cci]”[/cci]), you must repeat it twice. So you must write [cci]”[/cci] for single quote and [cci]””[/cci] for double quote. See below statement.

[sourcecode lang=”sql”]
SELECT ID, ”’Mr. ‘ || NAME || ”” || ‘ welcome to ""semurjengkol.com""’ FROM person;
[/sourcecode]

The statement will result:

[sourcecode lang=”plain”]
ID NAMA
1 ‘Mr. Ahmad’ welcome to "semurjengkol.com"
2 ‘Mr. Fatih’ welcome to "semurjengkol.com"
3 ‘Mr. Umar’ welcome to "semurjengkol.com"
[/sourcecode]