Oracle CONCAT

CONCAT is a string function which is used to combine two strings or expressions.

Syntax

The syntax of CONCAT () function is illustrated as follows:

CONCAT ( expression1, expression2);

Expression in this case is usually a string or column name which has string data types like CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

The functions returns a combined string by joining both the expressions.

The data type of the result will depend on the data types of input expressions. Suppose, we concatenate NCHAR datatype with NCLOB, the function returns NCLOB because conversion of data type is lossless in Oracle.

Oracle CONCAT Function Examples

1. Concatenate two string using CONCAT

Two input expressions are inserted in single quotes in a bracket.

Let’s see this simple query:

SELECT CONCAT('Have',' fun.') 
FROM dual;

Result:

The function joins both strings and returns a combined value.

2. Concatenate more than two strings

Lets have a look at this query which joins more than two strings.

SELECT CONCAT( CONCAT( 'Coding', ' is' ), ' fun.' ) 
FROM dual;

Result:

The query joins all the three strings together.

3. Using Concatenation Operator ||

Instead of writing CONCAT each time which gets cumbersome, we can instead use concatenation operator || in its place.

Here’s the syntax for using concatenation operator:

expression1 || expression2 || expression3 || …

SELECT 'Coding' || ' is' || ' fun.' 
FROM dual;

Result:

The query returns the same result as earlier but this time with lets effort.

4. Concatenate two columns in a table

Here, we will use Employee table for illustration:

Lets concatenate values of two columns in a table:

SELECT first_name || ' ' || last_name AS Full_name 
FROM employees;

Result:

The query concatenates first name and last name of employee and we use AS operator as an alias to rename the output column name.

Note:

To concatenate strings that contain single quote character, we simply add more single quote after a single quote to skip first single quote. ( ”)

Let’s see this in a query:

SELECT 'Let''s' ||' code.' 
FROM dual;

Result:

In this query, Let’s contained a single quote and we skipped it using one more single quote after that.

When we use two single quotes together during concatenation, Oracle skips the first one. We do this for combining strings with single quotes.

In this tutorial we learned how to use CONCAT function and concatenation operator( ||) in queries for combining strings.