How To Use CONCAT In Oracle SQL

This tutorial is based on examples so it would be easier to understand. Oracle Concat Function allows to merge or unite two strings and the following data types can used CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The function has two input parameters and it returns only one string. When the concatenating data types are different then Oracle database returns the data type that has less losses in the results of conversion. The syntax of Oracle Concat is:

CONCAT(<string_1>, <string_2>)

The first example is with the classical concatenating function and it merges two texts “Oracle ” (with a space in the end) and “database“. To avoid the two words would become an one word unless this is intentional we will use a space in the end of the first word. The SQL output shows below “TEXT” value “Oracle database“.

 SELECT CONCAT('Oracle ','database') as text
   FROM dual;

The second options to merge strings is to use the concatenation operator (||) that does the same as Oracle Concat function. The operator is more commonly used than the Concat function because it’s easier and the code stays simpler. The Oracle concatenation operator syntax is:

<string_1>'||'<string_2>

The second example returns the same output “Oracle database” as the last example and for concatenation we are using the same input strings: “Oracle ” (with a space in the end) and “database“. Take a look at the SQL query below and compare it with the first example.

 SELECT 'Oracle '||'database' as text 
   FROM dual;

Important To Know:
After looking at those last two examples you may got an idea to try the addition operator and unite those two words as on the following example.

 SELECT 'Oracle '+'database' as text
  FROM dual;

The output shows that you can’t concatenate them using the addition operator and the operator is used only with numbers as the error says “ORA-01722: invalid number“.

Now to merge three strings we have to use the Oracle Concat function 2 times as on the following example. The input strings are “Oracle ” (with a space in the end),”database” and “ help” (with a space in the beginning). The last word “ help” has space in the beginning and that shows you can add spaces where ever you want. The space in front of the third word is done for example and you can just add it in the end of the second string too.

 SELECT CONCAT(CONCAT('Oracle ','database'),' help') as text 
   FROM dual;

The output of last Select statement returned one string “Oracle database help” and that can be treated as one data type from now on. The next example does the same concatenation with the Concat operator and we are using the same three input string as above.

 SELECT 'Oracle '||'database'||' help' as text 
   FROM dual;

As you see from the last query using the operator (||) leaves the SQL query more simple and easier to read. The Oracle concatenation operator is more widely used because of that and you barely see the Concat function at all. The last example shows that you can add many operators you need into your code and the only restriction is that they can’t be in the beginning and the end of the string. In other words your string has to start and end with a data type.

 SELECT 'Oracle '||'database'||' help'||' from'||' a DBA' as text 
   FROM dual;

The final output became “Oracle database help from a DBA” and we are using 5 input strings.

See Also:
Oracle Select Oracle Substr Oracle Instr Home