Link for SQL concat function:
SQL Concat function/SQL concat() function
First function of SQL character manipulation function.
Two have in depth knowledge of SQL concatenation operator
Please watch my video on SQL concatenation Operator
Tool used in this tutorial is command prompt.
This tutorial series is part of SQL expert exam certification training. if you are preparing for SQL certification you can use my tutorials. This SQL Tutorial is a part of free training.
Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage
Link for SQL function introduction Video 10
Link SQL concat Article
Thanks for linking, commenting, sharing and watching more of our videos
This is Manish from RebellionRider.com
SQL Concat () function is a Character manipulation function which is a category of SQL character function. We have already discussed the intro of SQL character function in Video 10. You can find link of this video in description below.
Ok let's go ahead.
SQL Concat () function concatenates two separate character string into one character string.
Let's see the syntax
Concat (string_1 , String_2)
Note here that SQL Concat function takes only two arguments at a time.
This SQL Concat function will return string_1 concatenated with string_2.
This also means that it will return a single string which is a combined string of parameter String_1 and String_2.
Both the parameters of SQL Concat function String_1 and string_2 can be of any data-type. You can even specify the columns of the table here.
SQL Concat function is equivalent to the concatenation operator (||).
SQL concatenation operator is represented by double solid vertical bars or we can say double pipe signs.
Question: How to concatenate more than two strings as SQL Concat that can have only 2 parameters and what is the difference between SQL Concat Function and SQL concatenation operator?
The one answer for the second Question
What is the Difference between SQL Concat Function and SQL concatenation operator? Is
While SQL Concat function takes only two parameters, The Concat operator can be repeated as often as is necessary.
Meaning, by the help of SQL Concat operator you can combine as many strings as you want.
And Concatenation operator is also not supported by several databases such as SQL server so this might cause problem.
To answer the first question
How to concatenate more than two strings as SQL Concat that can have only 2 parameters?
We will have to jump over SQL developer.
Let's see some examples.
Say we want to retrieve the full name of an employee from Employees table.
Let's try it using SQL Concat function.
SELECT Concat (first_name, last_name) AS "Full Name" FROM employees;
Here in this query we use two column names - first_name and last_name as arguments of our Concat function
As you can see here, we get full name of our employee but there is no space in between first name and last name.
We will see how to format this string using SQL Concat function in a few seconds
But before that,
let's see how to do the same task using SQL concatenation operator.
SELECT first_name ||last_name AS "Full name" FROM employees;
As you can see this query also produces the same result.
Now we will see how to format full name of employees
First we will do this using SQL concatenation Operator.
SELECT first_name||' '||last_name AS "Full name" FROM employees;
Now you can see we get space between first name and last name of the employee.
Again, let's do it using SQL Concat function.
Here we will see the nested function concept where we will be using nested Concat function to achieve this formatted string.
SELECT Concat (Concat (first_name, ' '), last_name) FROM employees;
Inner Concat function has two parameters. These are our first column name first_name and an empty string which will be a space between first name and second name.
This Concat will now return a string which is the first name with space.
This returning string will be the first input of our Outer Concat and column last_name will be the second input.
All together this will give us the desired output.
As you can see this method is complex than the first one where we use SQL concatenation operator.