Introduction:

This tutorial explains how to use the Oracle SQL And PL/SQL || concatenate operator with basic syntax and examples. let’s get started

Oracle || operator used to Concatenates character strings and CLOB data.

Syntax:

string1 || string2

This operator if we used in SQL query OR PL/SQL program it will concatenates these two string or character values and the result will be another character string 🙂

Note:

– If both string or character are of type CHAR, the result has datatype CHAR and limit of 2000 characters.
– If both string or character are of type VARCHAR2, the result has datatype VARCHAR2 and limit of 4000 characters.

Examples:

-- Create dummy table to test different types of concatenations in SQL
CREATE TABLE testtab1 (
    col1_var  VARCHAR2(8),
    col2_cr   CHAR(8),
    col3_var  VARCHAR2(8),
    col4_cr   CHAR(8)
);

-- Insert dummy data into dummy table created above for test.
INSERT INTO testtab1 (col1_var
                     ,col2_cr
                     ,col3_var
                     ,col4_cr)
VALUES ('ora'
       ,'ask   '
       ,'dot   '
       ,'com');

-- test Query
SELECT col1_var || col2_cr || col3_var || col4_cr "Concatenation" FROM testtab1;

and the result will be :

Or another example based on suppliers table :

SELECT company_name || contact_name FROM suppliers;

Hope this help.