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.

SELECT 'semur' || 'jengkol' FROM dual

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

ID        NAME
1         Ahmad
2         Fatih
3         Umar

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

SELECT ID, 'Mr. ' || NAME FROM person;

Here the result:

ID        NAME
1         Mr. Ahmad
2         Mr. Fatih
3         Mr. Umar

Joining more than two strings

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

SELECT ID, "Mr. " || NAME || ', you are great' FROM person;

And the result is:

ID        NAME
1         Mr. Ahmad, you are great
2         Mr. Fatih, you are great
3         Mr. Umar, you are great

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.

SELECT ID, '''Mr. ' || NAME || '''' || ' welcome to ""semurjengkol.com""' FROM person;

The statement will result:

ID        NAMA
1         'Mr. Ahmad' welcome to "semurjengkol.com"
2         'Mr. Fatih' welcome to "semurjengkol.com"
3         'Mr. Umar' welcome to "semurjengkol.com"