This online tutorial page based on examples to be easier to follow. The Oracle Concat Function allows to merge or unite two string data types and the data types can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The function has two input parameters and it returns only one value. When the concatenating data types are different then Oracle database returns the data type that has less losses in the results of conversion. The Oracle Concat syntax is following:
CONCAT(<string_1>,<string_2>)
The first example is a classical concatenating to texts “Oracle ” (with a space in the end) and “database“. To avoid the two words would become a one 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. Operator Oracle concatenation 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;
You may have an idea to try the addition operator to join the same two strings as the following SQL example below.
SELECT 'Oracle '+'database' as text FROM dual;
The output says clearly that this is not a number (ORA-01722: invalid number) and this concatenation wouldn’t work.
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 it shows that 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 Online Tech Support Home