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.
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;
are two columns of hr. employees
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.
SQL> SELECT concat ('hello ','world!')FROM dual;
As you can see we have used two separate character strings “Hello”
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
Let’s see how.
('Hi!',''),'How'),' '),'are'),' '),'You'),'?')
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'||'?'
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
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