Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. Single Row Character Functions
  5. CONCAT

CONCAT

  • CONCAT is a single row character function.
  • It takes two strings as argument and returns String1 concatenated with String2.
  • CONCAT is equivalent to the concatenation operator (||). (||) is also called as pipe operator.
  • Result returns is a VARCHAR2.

Syntax: CONCAT(String1, String1)

SELECT 
    'The ' Str1, 
    'Code Man' Str2,
    CONCAT('The ', 'Code Man') Concat_Str
  FROM DUAL;

STR1 STR2     CONCAT_STR
---- -------- ------------
The  Code Man The Code Man
SELECT 
    'The ' Str1, 
    ' Code Man' Str2,
    'The ' || ' Code Man' Concat_Str
  FROM DUAL;

STR1 STR2      CONCAT_STR
---- --------- -------------
The   Code Man The  Code Man
  • CONCAT can be nested. You can nest CONCAT any number of times and can be nested with other functions too.
SELECT 
    'The ' Str1, 
    'Code' Str2, 
    ' Man' Str3,
    CONCAT(CONCAT('The ', 'Code'), ' Man') Concat_Str
  FROM DUAL;

STR1 STR2 STR3 CONCAT_STR
---- ---- ---- ------------
The  Code  Man The Code Man
SELECT 
    'the' Str1, 
    'codeman' Str2,
    CONCAT(INITCAP('the'), INITCAP('codeman')) Concat_Str
  FROM DUAL;

STR STR2    CONCAT_STR
--- ------- ----------
the codeman TheCodeman
SELECT 
    'the' Str1, 
    'code' Str2, 
    'man' Str3,
    CONCAT(CONCAT(CONCAT(CONCAT(INITCAP('the'),
                         CHR(32)),
                  INITCAP('code')),
           CHR(32)),
      INITCAP('man')) Concat_Str
  FROM DUAL;

STR STR2 STR CONCAT_STR
--- ---- --- ------------
the code man The Code Man

Difference between CONCAT function and pipe (||) operator

Everything that you do in CONCAT function can also be done by concatenation (||) operator. These are two different approaches that Oracle provides for doing the same thing.

But when more number of strings are concatenated, pipe operator makes the operation more simpler and readable compared to using CONCAT.

SELECT
    'Learn ' Str1, 
    'with ' Str2, 
    'The ' Str3, 
    'Code ' Str4, 
    'Man' Str5,
    CONCAT('Learn ',
        CONCAT('with ',
            CONCAT('The ',
                CONCAT('Code ', 'Man')
            )
         )
    ) Concat_Str
  FROM DUAL;

Doing the same using pipe operator.

SELECT
    'Learn ' Str1, 
    'with ' Str2, 
    'The ' Str3, 
    'Code ' Str4, 
    'Man' Str5,
    'Learn '    || 
        'with ' || 
        'The '  || 
        'Code ' || 
        'Man' Concat_Str
  FROM DUAL;
Was this article helpful to you? Yes No

How can we help?