SQL CONCAT() Function

SQL CONCAT() Function

The SQL CONCAT() function is used to concatenate or joins strings from one or more string or expression.

The SQL CONCAT() function is supports or work with character expression and also many databases supports numeric based expressions.

It can be used in any valid SQL SELECT statement as well in SQL where clause.

Sql concate function using concat sql server, concatenate string, join strings, concatenate two columns, string append text, alias, concat if not null.


The below syntax is used to concatenate strings from a given input string or expression.


SELECT CONCAT(string1, string2, ..., stringN);


SELECT CONCAT(string1, string2);

Note: The Oracle CONCAT() function will support only 2 string at once.

The below syntax is how to concatenate multiple strings in oracle:

SELECT CONCAT(CONCAT(string1, string2), string3);


SELECT string1 & string2;

Note: The CONCAT() function will not support by ms access. It uses "&" operator to concatenate a strings.

The below syntax is used to concatenate strings from a in a specific column value.


SELECT CONCAT(column_name1, column_name2) FROM table_name;


SELECT column_name1 & column_name2 & ...column_nameN FROM table_name;

SQL CONCAT() Example - Using Expression Or Formula

The following SQL SELECT statement concatenate three strings as a single string.

SELECT CONCAT('Sql', ' ', 'Tutorial') AS 'Concatenated Strings';

The result of above query is:

Concatenated Strings
Sql Tutorial

SQL CONCAT() Function More Example

Input Value Result
CONCAT('Hi!') Hi!
CONCAT('Hi!', 'Hello') Hi!Hello
CONCAT('Welcome ', 'To ', 'Simmanchith ', '.com') Welcome To Simmanchith.com
CONCAT('Simmanchith', '.', 'com') Simmanchith.com
CONCAT('James', ' ', 'Bond', ' ', '007') James Bond 007

Sample Database Table - Employee

ID FName LName Gender Age City State
111 Suresh Babu Male 32 Nasik MH
222 Siva Kumar Male 22 Chennai TN
333 Bala Murugan Male 33 Nasik MH
444 Bala Karthik Male 20 Madurai TN

SQL CONCAT() Example

The following SQL statement concatenate the "FName" and "LName" column from the "Employee" table and produce a new column called as "EmpName" to the result set:


SELECT ID, CONCAT(FName, ' ', LName) As 'Emp Name', 
City FROM Employee;

For MS Access

SELECT ID, FName & ' ' & LName As 'Emp Name', 
City FROM Employee;

The result of above query is:

ID Emp Name City
111 Suresh Babu Nasik
222 Siva Kumar Chennai
333 Bala Murugan Nasik
444 Bala Karthik Madurai

Sql server concat using mssql string concatenation, sql server string join, append string, sql functions, sql append string to field, group concat, multiple columns, concat ws, multiple strings.

SQL Concat Alias

CONCAT(), perhaps most useful of the text functions, is worth discussing separately, along with its regular SQL companion alias. The CONCAT() function completes the join, which is a fancy word for adding multiple values ​​together.

The concatenation syntax requires that you include, in parentheses, the different values ​​that you want to put together, in order, and separated by commas:

CONCAT(column1, column2)

While you can and often will apply CONCAT() to columns, you can also embed strings, single-quoted entries. To format a person's name as a last name, first from two columns you would use,

CONCAT(last_name, `, `, first_name)

Since join is used to create a new value, you'll want a new way to refer to the returned result.

This is where the concept of aliasing in SQL comes in. The alias is simply a symbolic name change. This works using AS terminology:

SELECT CONCAT(last_name, ', ', first_name) AS name FROM users

The result of this query would be that all users in the table would have their name formatted as you might want it displayed, and the returned column would be called name.

You can, in your queries, make an alias of any column or table. That general syntax is:

SELECT column AS alias_name FROM table AS tbl_alias

Example 1:

Step 1: Display all of the client address information as one value :

SELECT client_name, CONCAT(client_street, ', ', client_city, ', ', client_state, ' ',
client_zip) AS address FROM clients;

This to begin with utilize of the CONCAT() work amasses all of the address data into one flawless column, renamed address (see the figure). In the event that you needed, you may add WHERE client_street Isn't Invalid and client_city Isn't Invalid and client_state Isn't Invalid to the inquiry to run the show out inadequate addresses. (Otherwise you may fair include one of those three clauses.)

Step 2: Select every expense, along with its description and category:

SELECT expense_amount, expense_date,
CONCAT(expense_category, ': ', expense_description) FROM expenses,
expense_categories WHERE
expenses.expense_category_id = expense_categories.expense_ category_id;

In this inquiry, I have performed a connect so that I can show both cost and cost category data at the same time. The concatenation takes put over two columns from two diverse tables.

If you see at the column names within the picture, you'll see the result of utilizing capacities without aliases.

Step 3: Show the three most-expensive invoices, along with the client name and identification number

The CONCAT() and alias techniques can be applied to any query, including joins.

SELECT invoices.*,
CONCAT(client_name, ' - ', clients.client_id) AS client_info
FROM invoices LEFT JOIN clients
USING (client_id)
ORDER BY invoice_amount DESC

To perform this inquiry, I utilize a cleared out connect, arrange the comes about by the invoice_amount, and constrain the comes about to fair three records. The CONCAT() work is connected to the client's title and ID.

So that the comes about are less demanding to scrutinize, I utilize the G modifier to end the inquiry (typically a include of mysql).

Step 4: Simplify the query from Step 2 using aliases for table names:

Simplify the query from Step 2 using aliases for table names.

I've simplified my queries, without affecting the end result, by using aliases for my table names.

SELECT expense_amount, expense_date,
CONCAT(expense_category, ': ', expense_description) FROM
expenses AS e,
expense_categories AS e_c
WHERE e.expense_category_id = e_c.expense_category_id;

The inquiry itself is the same because it was in Step 2 but that I have rearranged writing it by utilizing assumed names for the table names. In wordy queries and joins, this is often a decent shorthand to use.

Notes: CONCAT() includes a culmination work called CONCAT_WS(), which stands for with separator.


CONCAT_WS(separator, column1, column2, ...).

The delimiter will be inserted between each column listed. A nice feature of this function is that it ignores all NULL values.

An alias can contain up to 255 characters and is always case sensitive.

The AS term used to create the alias is optional. You can write a simpler query like:

SELECT column alias_name FROM table

Example 2: By default, MySQL uses the name of the original column as declared in the table as the heading for the column in the results of your query.

SELECT bookName
FROM books


The Great Gatsby

Example 3: The heading of a column is frequently insufficiently descriptive. The column header will also be altered by various operations, such as text concatenation or mathematical calculation, making it much harder to read and comprehend:

SELECT CONCAT('hello', ' ' , bookName)
FROM books


CONCAT('hello', ' ', bookname)
Hello The Great Gatsby
Hello Dune

SQL Concat Group

Using GROUP BY in MySQL, you must use GROUP_CONCAT() with a SEPARATOR option, which can be a comma, a space, etc.


SELECT yourColumnName1,
GROUP_CONCAT(yourColumnName2 SEPARATOR ‘yourValue’) as anyVariableName 
FROM yourTableName GROUP BY yourColumnName1;

Example 1:

Step 1: let us create a table. The query to create a table is as follows:

create table GroupConcatenateDemo(id int, name varchar(50));

Step 2: Insert some records in the table using insert command.

mysql> insert into GroupConcatenateDemo values(10,'Larry');
mysql> insert into GroupConcatenateDemo values(11,'Mike');
mysql> insert into GroupConcatenateDemo values(12,'John');
mysql> insert into GroupConcatenateDemo values(10,'Elon');
mysql> insert into GroupConcatenateDemo values(10,'Bob');
mysql> insert into GroupConcatenateDemo values(11,'Sam');

Step 3: Display all records from the table using select statement. The query is as follows:

mysql> select *from GroupConcatenateDemo;


Id Name
10 Larry
11 Mike
12 John
10 Elon
10 Bob
11 Sam

Step 4: Here is the MySQL query that concatenates the texts using GROUP BY. Utilize MySQL's GROUP_CONCAT() function to concatenate the strings after doing GROUP BY based on Id.

mysql> select Id, 
group_concat(Name SEPARATOR ',') as GroupConcatDemo from GroupConcatenateDemo
group by Id;



Id GroupConcatDemo
10 Larry,Elon,Bob
11 Mike,Sam
12 John

Example 2: For those who are unaware, grouping rows of data together into a single string is known as "grouped concatenation" (usually with delimiters like commas, tabs, or spaces). This could be referred to as a "horizontal join." Here is a brief illustration showing how to compress a list of pets owned by each member of the family, from the normalised source to the "flattened" output:

Step 1: Create table

Throughout the years, there have been numerous approaches to solving this issue; the following are just a handful, based on the sample data below:

CREATE TABLE dbo.FamilyMemberPets

Step 2: Insert Data

INSERT dbo.FamilyMemberPets(Name,Pet) VALUES
(N'Henry',   N'Piglet'),
(N'Lisa',    N'Snowball'),
(N'Lisa',    N'Snowball II');

Step 3: I won't illustrate every grouped concatenation technique ever developed because I want to concentrate on a few features of my suggested approach, but I will describe some of the more popular ones anyway:

Scalar UDF
CREATE FUNCTION dbo.ConcatFunction
  SELECT @s = COALESCE(@s + N', ', N'') + Pet
    FROM dbo.FamilyMemberPets
	WHERE Name = @Name
  RETURN (@s);
SELECT Name, Pets = dbo.ConcatFunction(Name)
  FROM dbo.FamilyMemberPets
  ORDER BY Name;

Note: there is a reason we don't do this:

SELECT DISTINCT Name, Pets = dbo.ConcatFunction(Name)
  FROM dbo.FamilyMemberPets
  ORDER BY Name;

With DISTINCT, the function is run for every single row, then duplicates are removed; with GROUP BY, the duplicates are removed first.

Common Language Runtime (CLR)

This uses the GROUP_CONCAT_S function found at http://groupconcat.codeplex.com/:

SELECT Name, Pets = dbo.GROUP_CONCAT_S(Pet, 1)
  FROM dbo.FamilyMemberPets
  ORDER BY Name;

SQL Concat Group-Concat

The function GROUP_CONCAT joins or concatenates data from various rows into a single field. It is a GROUP BY function that, if the group contains at least one non-null value, returns a string; otherwise, it returns a numeric value.

1. Introduction to MySQL GROUP_CONCAT() function:

With a variety of arguments, the MySQL GROUP_CONCAT() function concatenates strings from a group into a single string.

Syntax: The following shows the syntax of the GROUP_CONCAT() function:

    DISTINCT expression
    ORDER BY expression

Method 1:

Example 1: The following example demonstrates how the GROUP_CONCAT() function works.

Step 1: create table:

    v CHAR

Step 2: insert table:

INSERT INTO t(v) VALUES('A'),('B'),('C'),('B');

Step 3: Display Records:

        ORDER BY v ASC
        SEPARATOR ';')

Before concatenating the elements in the group, you can remove duplicate values using the DISTINCT clause.

Before concatenating, you can order the values using the ORDER BY clause in either ascending or descending order. The values are sorted by default in ascending order. You must explicitly choose the DESC option if you want to sort the values in descending order.

A literal value is put between group members according to the SEPARATOR specification. The comma (,) is used as the default separator by the GROUP_CONCAT function if you do not specify another separator.

NULL values are disregarded by the GROUP_CONCAT function. If neither a matched row nor any parameters have values, it returns NULL.

Depending on the parameters, the GROUP_CONCAT function either returns a binary or non-binary string. The return string's maximum length is 1024 by default. The group_concat_max_len system variable can be adjusted at the SESSION or GLOBAL level to increase the maximum length if you need it to be longer.

2. MySQL GROUP_CONCAT() function examples:

Example 1: Let’s take a look at the customers table in the sample database.

To get all countries where customers locate as a comma-separated string, you use the GROUP_CONCAT() function as follows:


However, some customers located in the same country.

Example 2: To remove the duplicate country’s names, you add the DISTINCT clause as the following query:


It is more readable if the country’s names are in ascending order.

Example 3: To sort the country’s name before concatenating, you use the ORDER BY clause as follows:

        ORDER BY country)

Example 4: To change the default separator of the returned string from a comma (,) to a semi-colon (;), you use the SEPARATOR clause as the following query:

        ORDER BY country
        SEPARATOR ';')

Great! now you know how the GROUP_CONCAT() function works. Let’s put it in a practical example.

Example 5: There is at least one sales agent for every customer. In other words, each salesperson is in charge of a certain number of clients. You can use the inner join clause as follows to determine who is in charge of which customers:

SELECT employeeNumber, 
        INNER JOIN
    customers ON customers.salesRepEmployeeNumber = employees.employeeNumber

Example 6: Using the GROUP_CONCAT() function, we can group the result set by the employee number and concatenate all the employees who are in charge of the employee as seen below:

SELECT employeeNumber,
    GROUP_CONCAT(DISTINCT customername
        ORDER BY customerName)
        INNER JOIN
    customers ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber
ORDER BY firstName , lastname;

The result set is much easier to read.

3. Using MySQL GROUP_CONCAT() with CONCAT_WS() function example:

Sometimes, the GROUP_CONCAT function can be combined with the CONCAT_WS function to make a result of query more useful.

Example: For example, to make a list of semicolon-separated values of customers:

  • The CONCAT_WS() method is used to first concatenate the last and first name of each customer's contact. The contact's complete name appears as a result.
  • The list is then created by using the GROUP_CONCAT() method.

The following query creates a list of customer values with semicolons separating them.

   CONCAT_WS(', ', contactLastName, contactFirstName)

Note that the GROUP_CONCAT() function concatenates string values ​​in different rows while the CONCAT_WS() or CONCAT() function concatenates two or more string values ​​in different columns.

4. MySQL GROUP_CONCAT function: common mistakes

The GROUP_CONCAT() function returns a single string, not a list of values. It means you cannot use the result of the GROUP_CONCAT() function for IN operator e.g., within a subquery.

Example 1: For example, the GROUP_CONCAT() function returns the results of the values: 1 2 and 3 as the string '1,2,3'.

If you provide this result for the IN operator, the query does not work. Therefore, the query may not return results. For example, the following query will not work as expected.

Because the IN operator accepts a list of values, for example (1,2,3) rather than a string consisting of a list of values ​​('1,2,3'). Therefore, the following query will not work as expected.

SELECT id, name
    id IN GROUP_CONCAT(id);

Due to the fact that the GROUP_CONCAT function is an aggregate function, sorting the results requires using the ORDER BY clause within the function rather than the ORDER BY in the SELECT statement.

Example 2: The following example demonstrates the incorrect usage of the ORDER BY clause in the context of using the GROUP_CONCAT function:

        SEPARATOR ';')
ORDER BY country;

The SELECT clause returns one string value so the ORDER BY clause does not take any effect in this statement.

5. MySQL GROUP_CONCAT() function applications:

The GROUP_CONCAT() method can be used in a variety of situations to deliver beneficial outcomes. The GROUP_CONCAT() function is commonly used in the following situations.

  • Create user roles with commas between them, such as "admin," "author," and "editor."
  • Create the user's hobbies, separated by commas, for example, "design, programming, reading."

For example, "mysql, mysql aggregate function, mysql pages" could be a tag for a blog post, an essay, or a product.

In this tutorial, you have learned how to use the MySQL GROUP_CONCAT() function to concatenate non-NULL values of a group of strings into a single string.

Example: If we wish to separate the values by a different operator, we would pass the operator in the string literal.

emp_id first_name last_name quality dep_id
1 George Cleverly Speaks well 123
2 Tom Rooney Manages well 123
1 George Cleverly Punctual 123
3 Clarke James Quick worker 451
3 Clarke James Manages well 451
4 Hill Billings Quick worker 451

Step 1: Simple GROUP_CONCAT:

This example concatenates the qualities of each employee into a single field.

SELECT emp_id, first_name, last_name, dep_id, 
GROUP_CONCAT( quality ) as "qualities" 
FROM Employee group by emp_id;

Step 2: Distinct clause:

This example concatenates the distinct qualities for each department.

SELECT dep_id, 
as "Employee qualities"  

Step 3: ORDER BY and SEPARATOR clause:

Here the Separator ‘_’ will separate the values with an underscore (_), and a space before and after '_’.

SELECT dep_id, 
as "Employee ids" 
from Employee group by dep_id;

Method 2:

STRING_AGG() function which is available from SQL Server 2017. This function does pretty much the same thing as MySQL’s GROUP_CONCAT() function (with some minor differences).

Syntax: The syntax of the STRING_AGG() function goes like this:

STRING_AGG ( expression, separator ) [ < order_clause> ]

< order_clause> ::=   
WITHIN GROUP ( ORDER BY < order_by_expression_list > [ ASC | DESC ] )

Where expression is any kind of expression. Concatenation transforms expressions into NVARCHAR or VARCHAR types. Types that are not strings are changed to NVARCHAR types.

Where separator is a string that separates concatenated strings, often of the NVARCHAR or VARCHAR type. It may be variable or literal.

The WITHIN GROUP is followed by ORDER BY ASC or ORDER BY DESC in parentheses to form the (optional) order clause. The result is arranged according to ASC. This is the preset option. The result is sorted by DESC in descending order.

Example 1: Here’s a quick example of the STRING_AGG() function:

SELECT STRING_AGG(Genre, ',') AS Result
FROM Genres;


Rock,Jazz,Country,Pop,Blues,Hip Hop,Rap,Punk

As you can see, the result set is presented as a list with commas between each item. This is so because our second argument, which is a comma, instructs the computer to use a comma as the separator.

Example 2: Here’s the same result set but without the STRING_AGG() function:

SELECT Genre AS Result 
FROM Genres;


Hip Hop

SQL Concat Multiple Columns

There are two functions for doing this:


Both functions work similarly but have little difference.


Concatenating many columns or strings into a single one is done with this function. A comma is used to divide each argument.

Syntax :

CONCAT( column1, column2, ... )


CONCAT ( string1, string2, ... )

Example 1: Concatenating the data while choosing rows from the DataBase Table, however, will make the aforementioned procedure a little bit easier.

Let’s take a simple example:

You have two columns – firstname, lastname within your DataBase Table you want to show both the columns values in a single string form. In this case, you can use MySQL functions to combine the values of the columns.

For demonstration, I am using Users Table which has the following records.

id username firstname lastname
1 yssyogesh Yogesh Singh
2 sonarika Sonarika Bhadoria
3 vishal Vishal Sahu

I am using this function to concatenate firstname, lastname columns and set it ALIAS to fullname.

CONCAT( firstname, " ", lastname ) AS fullname 
FROM users


id username fullname
1 yssyogesh Yogesh Singh
2 sonarika Sonarika Bhadoria
3 vishal Vishal Sahu

Example 2: Suppose the current table in MySQL contains the following fields.

firstName lastName
Will Smith
John Doe

We want to create a new column fullName that contains both fields.

Will Smith
John Doe

This concatenation can be done through a simple CONCAT operation.

fullName = CONCAT(firstName, ' ', lastName)

Other table columns can be referred to in our DEFAULT expression if we're using MySQL v8.0 (as explained in the docs). The approach described here will be required if we're running MySQL v5.7 or earlier.


The CONCAT_WS() function not only adds multiple string values and makes them a single string value. It also let you define separator ( ” “, ” , “, ” – “,” _ “, etc.).

Syntax :

CONCAT_WS( SEPERATOR, column1, column2, ... )


CONCAT ( SEPERATOR, string1, string2, ... )

Example 1:

CONCAT_WS( " ", firstname, lastname ) AS fullname 
FROM users


id username fullname
1 yssyogesh Yogesh Singh
2 sonarika Sonarika Bhadoria
3 vishal Vishal Sahu

As I said at the start of the function you can define any other characters instead of space.

SQL Concat Order By

The non-NULL values from a group are concatenated into a string as the result of this function. If there are no non-NULL values, it returns NULL.

The group_concat_max len server system variable, which has a default value of 1M (>= MariaDB 10.2.4) or 1K (= MariaDB 10.2.3), controls the maximum returned length in bytes.

The result type is VARBINARY or VARCHAR if group_concat_max_len = 512; otherwise, it is BLOB or TEXT. The input determines whether to use binary or non-binary types.


GROUP_CONCAT([DISTINCT] expr [,expr ...]
    [ORDER BY {unsigned_integer | col_name | expr}
    [ASC | DESC] [,col_name ...]]
    [SEPARATOR str_val]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}])

DISTINCT eliminates duplicate values from the output string.

The order of the values returned is determined by ORDER BY.

A separator between the values is specified by SEPARATOR. The comma is the standard separator (,). By giving an empty string, a separator can be avoided.

Example 1: You want to sort the rows by exam date.

FROM exam
ORDER BY STR_TO_DATE(CONCAT(exam_year, ' ', exam_month, ' ', exam_day), '%Y %M %d');

The result looks like this (the rows are sorted in ascending order by exam_year, exam_month, and exam_date):

subject exam_year exam_month exam_day
Mathematics 2019 December 19
Health 2020 January 5
Science 2020 January 5
English 2020 January 8


Create date values from the values for the year, the month, and the day to sort by dates. Use the STR_TO_DATE() method to accomplish this. Use STR_TO_DATE(date string, "%Y %M %d") to convert a date that is saved as a string in the "Year Month Day" format into a date.

But first, you need to create a string using the CONCAT() function:

CONCAT(exam_year, ' ', exam_month, ' ', exam_day)

The CONCAT() function combines all the arguments into one string. You don't need to cast numbers to strings. Since you'd like to get a string in the 'Year Month Day' format, the arguments are exam_year, exam_month, exam_day, and the spaces between them.

Then, you need to convert this string to a date using the STR_TO_DATE(date_string, '%Y %M %d') function. The second argument of this function is the date format. %Y stands for year, %M stands for month (its full name, not a number), and %d stands for day.

STR_TO_DATE(CONCAT(exam_year, ' ', exam_month, ' ', exam_day), '%Y %M %d')

Example 2: To order the rows according to date in ascending order, combine it with an ORDER BY clause. Just add the phrase DESC to the end, as in the following example, to display the rows in decreasing order:

FROM exam
ORDER BY STR_TO_DATE(CONCAT(exam_year, ' ', exam_month, ' ', exam_day), '%Y %M %d') DESC;

SQL Concat Space

We can use the space function to join two columns of the string type that are separated by a space.


SELECT FirstName + SPACE(1) + LastName
FROM PersonalDetails

You'll see that between FirstName and LastName is the SPACE(1) function. FirstName and LastName column values will be separated by a single blank space because the parameter value supplied to the SPACE function is 1.

SQL Concat String Int

You can concatenate or combine two or more string values using + operator in SQL Server.

Example 1: The + operator returns a string that is the result of concatenating two or more string values.

Declare @str1 varchar(50) ='hello'
Declare @str2 varchar(50) ='world'
SELECT (@str1+' '+@str2) as Result

Example 2: Using the + operator, you can also concatenate two values of various data types. However, before concatenating the values, you must turn each value into a single data type.

Concatenate string and Int as string value in SQL server

Declare @str1 varchar(50) ='hello'
Declare @int1 int =10
SELECT (@str1+' '+cast(@int1 as varchar(50))) as Result

Concatenate string and Int as integer value in SQL server

Declare @str1 varchar(50) ='100'
Declare @int1 int =10
SELECT (cast(@str1 as int)+@int1) as Result

Note: If you are using SQL Server 2012 or a later version, you can use the CONCAT function to concatenate or combine two or more string values.

Example 3: The CONCAT function creates a string by joining two or more string values together.

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;

You will get below error, if your run this query in older version of SQL Server like SQL Server 2008 r2.

Msg 195, Level 15, State 10, Line 1
'CONCAT' is not a recognized built-in function name.

SQL Concat Where

When performing queries against a large table and using a function such as CONCAT() in the WHERE clause, the queries can be much slower than if the function was not used. This usually happens when the function is used on an indexed column.

Example 1: Suppose, in the table 'Student', we want to join the column values, 'Name', 'Address' and 'Column', based on the condition that is also a join of the column values. , 'Name','Subject', provided in the WHERE clause using the CONCAT() function. We can use the following query to get the output:

Select CONCAT(Name, ' ', 'Resident of', ' ', Address,' ','is',' ', 'Studying',' ', Subject)AS 'Detail of Student' 
from Student WHERE CONCAT(Name, Subject) = "AaravHistory";


Detail of Student
Aarav Resident of Mumbai is Studying History

Example 2: As an instance, assume a table holds records on nation and has a pillar each customer's given name and family name. Suppose further that these name processions are ordered. Based on these suppositions, the following SQL charge hopeful more gradual on account of the CONCAT() function in the WHERE paragraph:

SELECT id, CONCAT(name_first, ' ', name_last), city 
FROM customers 
WHERE CONCAT(name_first, ' ', name_last) = 'John Smith';

Example 3: If you process an ordered procession accompanying a function in a WHERE passage, it cannot be secondhand as an index to fast establish corresponding rows. The query function in the WHERE passage in the instance above causes a thorough table leaf through expected acted. MySQL will execute CONCAT() on the name lines for each sole row to create the corresponding to the client name 'John Smith'. To be intelligent to use the arranged processions (the name columns), for all practical purposes the following endure be filed alternatively:

SELECT id, CONCAT(name_first, ' ', name_last), city 
FROM customers 
WHERE name_first = 'John ' AND name_last = 'Smith';

SQL Concat WS

Main Article :- Sql difference between CONCAT() and CONCAT_WS() Functions

CONCAT_WS() signifies Concatenate With Separator and is a distinguished form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is additional between the series expected concatenated. The separator maybe a string, as can the rest of the arguments.

1. Syntax

concat_ws(sep [, expr1 [, ...] ])

2. Arguments

  • sep: An STRING expression.
  • exprN: Each exprN can be either a STRING or an ARRAY of STRING.
  • Returns: The result type is STRING.

The result is NULL if the separator is NULL; all other NULL principles are ignored. An empty series results if only the separator is known or if all exprN are NULL. This form CONCAT_WS() is appropriate when you want to concatenate a few values and stop all data from being useless if one of the authorities is NULL.

Example 1:

SELECT concat_ws(' ', 'Spark', 'SQL');


Spark SQL

SELECT concat_ws('s');



SELECT concat_ws(',', 'Spark', array('S', 'Q', NULL, 'L'), NULL);



Example 2: To separate the strings with a separator, we can use CONCAT_WS() function as well.

Take a look at an example below to understand how it work.




So you can concatenate strings in SQL using either of these methods. Let's take another look at the CONCAT function's input arguments.

CONCAT_WS Parameters - The separator that you want to use is the first parameter, followed by a comma to separate each string that will be concatenated.

Example 3:

SELECT CONCAT_WS(',','First name','Second name','Last Name');


CONCAT_WS(',','First name','Second name','Last Name')
First name,Second name,Last Name

Example 4:

SELECT CONCAT_WS('-','Floor',NULL,'Room');



Example 5: In some cases, remember to include a space in the separator string:

SET @a = 'gnu', @b = 'penguin', @c = 'sea lion';

SELECT CONCAT_WS(', ', @a, @b, @c);


CONCAT_WS(', ', @a, @b, @c)
gnu, penguin, sea lion

Example 6: Using CONCAT_WS() to handle NULLs:

SET @a = 'a', @b = NULL, @c = 'c';

SELECT CONCAT_WS('', @a, @b, @c);


CONCAT_WS(', ', @a, @b, @c)

Example 6: Simply include that as the first argument to serve as a separator. Put the string and number as the second and third arguments after that.

SELECT CONCAT_WS(': ', 'Comments', 9) AS Result;


Comments: 9

Given that the separator is inserted between each individual item that is concatenated, it should be noted that this function is not always appropriate for separating name/value pairs (so it will also be added between each pair in the event you have multiple pairs). Since we only need to concatenate one name/value combination, it works perfectly in our scenario.

To return the appropriate concatenation for your purposes, you can always nest additional CONCAT_WS() routines (so that you can have a different separator between each pair)

SQL Concat Multiple Strings

The SQL Server method CONCAT is used to combine two or more strings or to combine multiple strings into a single one.

To put it another way, only two strings can be joined together at once. However, Oracle allows us to concatenate many strings at once by using the operator "||".


CONCAT ( input_string1, input_string2 [, input_stringN ] );

The SQL CONCAT() takes two until 255 recommendation series and joins them into individual. Therefore, it demands not completely two recommendation strings. If you pass individual recommendation string, the CONCAT() function will raise an mistake.

Furthermore, if you pass the non-personality string principles, therefore the SQL CONCAT() function will implicitly convert those principles into successions before concatenating.The CONCAT in SQL, a function too converts NULL into an unfilled string accompanying the type VARCHAR(1).

Note: To add a separator all the while the concatenation, before you will use the CONCAT_WS() function.

CONCAT SQL Function Examples: Let’s see the some example to get familiar with the SQL CONCAT() function.

Example 1: Using SQL CONCAT() function with literal strings:

The following given below example uses the CONCAT SQL function to concatenate three literal string David, space, and Shayala:

SELECT 'David' + ' ' + 'Shayala' AS full_name;


David Shayala

Example 2: Using CONCAT() function with table columns:

The following statement uses the SQL CONCAT() function to concatenate values in the CustomerName, City, Country, and PostalCode columns of the customers table:

SELECT CustomerName,
    CONCAT(CustomerName, ' ', City, '', Country, '', PostalCode) full_address

Accordingly, as you can visualize in the output if the consumers destitute to suggest the country name, therefore the CONCAT() function secondhand an empty for the connection.

Note: we used the SQL CHAR() function to get the new line character in this example.

Example 3: Let's look at a few examples. Suppose we have the following table:

Geography table

region_name store_name
East Boston
East New York
West Los Angeles
West San Diego


SELECT CONCAT(region_name,store_name) FROM Geography WHERE store_name ='Boston';



Example 5: Oracle:

SELECT region_name || '' || store_name FROM Geography WHERE store_name ='Boston';



Example 6: SQL Server:

SELECT region_name + '' + store_name FROM Geography WHERE store_name ='Boston';



SQL Concat If Not Null

I have this table:


id name
10 Mike

I want to select id and name from the table and use concat on id, but only if it's not null, like this:

Id Name
A10 Mike

Example 1: I've tried the following:

SELECT ISNULL(concat('A', id), NULL) AS id, name FROM PeronTable


Id Name
A10 Mike
A Jane

Example 2: You can use + instead of concat():

select 'A' + convert(varchar(255), id), name
from t;

convert() (or cast()) is necessary assuming that id is a number and not a string.

+ returns NULL if any argument is NULL; concat() ignores NULL arguments.

Example 3: you can use concat() with a case expression:

select (case when id is not null then concat('A', id) end), name
from t;

SQL Concat Date Time

To combine date and time column into a timestamp, you can use cast() function with concat().


select cast(concat(yourDateColumnName, ' ', yourTimeColumnName) as datetime) 
as anyVariableName from yourTableName;

In the above concept, you will use cast() when your date and time is in string format. The cast() function can be used only for datetime.


Step 1: let us create a table.

mysql> create table DateAndTimeToTimestamp
  Duedate date,
  DueTime time

Step 2: Insert records in the table using insert command.

mysql> insert into DateAndTimeToTimestamp values(date_add(curdate(),interval 2 day),'10:30:02');
Query OK, 1 row affected (0.34 sec)

mysql> insert into DateAndTimeToTimestamp values(date_add(curdate(),interval -2 day),'12:20:45');
Query OK, 1 row affected (0.17 sec)

mysql> insert into DateAndTimeToTimestamp values(date_add(curdate(),interval 1 day),'01:32:42');
Query OK, 1 row affected (0.21 sec)

mysql> insert into DateAndTimeToTimestamp values(date_add(curdate(),interval -1 day),'14:25:58');
Query OK, 1 row affected (0.30 sec)

Step 3: Display all records from the table using select statement.

mysql> select *from DateAndTimeToTimestamp;


Duedate DueTime
2018-12-16 10:30:02
2018-12-12 12:20:45
2018-12-15 01:32:42
2018-12-13 14:25:58

Step 4: Here is the query to combine date and time column into timestamp :

mysql> select concat(Duedate, ' ', DueTime) as timestampDemo from DateAndTimeToTimestamp;


2018-12-16 10:30:02
2018-12-12 12:20:45
2018-12-15 01:32:42
2018-12-13 14:25:58