By Manish Sharma

Character Manipulation Functions

SQL Concat Function.

As we have already seen the hierarchy of SQL functions in my previous article SQL Concat () function is a character manipulation SQL function. As the name suggests SQL Concat () function concatenates or combines two separate character strings and returns a single character strings.

Syntax: CONCAT (string_1, String_2)
In oracle, the SQL Concat () function takes only two arguments. These arguments can be of any data-types CHAR, VARCHAR2, NCHAR, NVARCHAR, and CLOB or NLOB. You can even specify the columns of the table here.

For example

Say if you want to retrieve full name of employee from employees table then you can use SQL Concat () function as

SQL> SELECT Concat (first_name, last_name) FROM employees;
Here first_name and last_name are two columns of hr. employees table.

The output of this query will be the full name of employee without any space between first name and last name. And if you want to concatenate two strings then you can specify two separate character strings as arguments of SQL Concat function.

For example

SQL> SELECT concat ('hello ','world!')FROM dual;
As you can see we have used two separate character strings “Hello” and “World!” here as arguments to our SQL concat function. On execution the output of this query will be Hello World!
So from the above two examples it’s clear that SQL concat function returns a single string which is a combined string of parameter String_1 and String_2.

SQL concat function is equivalent to SQL concat operator?

Yes, it is true that both SQL concat function and SQL concat operator perform the same task. Then question arises here that what is the difference between SQL concat function and SQL concatenation operator?

Both SQL concat function and SQL concatenation operator have their advantages and disadvantages.

While SQL concat function takes only two parameters, SQL concat operator can be repeated as often as is necessary.

As we have seen above that we can only provide two arguments to SQL concat function neither more than two nor less than two but with SQL concatenation operator scenario is totally different. With SQL concatenation operator you can combine as many strings as you want

How to combine more than two strings using SQL concat function?

Now you must be thinking that How to combine more than two strings using SQL concat function that can have only two parameters?

Although SQL concat takes only two parameters you can still combine more than two strings. But for that you will have to use the concept of Nesting SQL concat function.

How? Let me explain.

Say you want to concatenate the string - ‘Hi! How are you?’ using SQL concat function. For that we will be nesting concat function.

Let’s see how.
       ('Hi!',''),'How'),' '),'are'),' '),'You'),'?')
FROM dual;
As you can see here that just to concat a single string “Hi! How are you?” We have to use seven concat functions.

Thanks to SQL developer who helps us in remembering all the parenthesis phewww!! 

Incidentally this task can be achieved much easily if you use SQL concatenation operator.

Let’s see how
       'Hi!'||' '||'How'||' '||'are'||' '||'you'||'?'
FROM dual;
So what you think? Which one is easier? SQL concat function or SQL concatenation operator?

You can also watch my video on this topic.

Hope you enjoyed reading.
You can also Like & Share this article on your socials.


SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

Presentation used in Youtube Video
SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn