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.

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
LIMIT 3\G

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.

Syntax:

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

Output:

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

Output:

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

Syntax:

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;

Output:

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;

Output:

Output:

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
(
  Name SYSNAME,
  Pet SYSNAME,
  PRIMARY KEY(Name,Pet)
);

Step 2: Insert Data

INSERT dbo.FamilyMemberPets(Name,Pet) VALUES
(N'Madeline',N'Kirby'),
(N'Madeline',N'Quigley'),
(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
(
  @Name SYSNAME
)
RETURNS NVARCHAR(MAX)
WITH SCHEMABINDING 
AS 
BEGIN
  DECLARE @s NVARCHAR(MAX);
 
  SELECT @s = COALESCE(@s + N', ', N'') + Pet
    FROM dbo.FamilyMemberPets
	WHERE Name = @Name
	ORDER BY Pet;
 
  RETURN (@s);
END
GO
 
SELECT Name, Pets = dbo.ConcatFunction(Name)
  FROM dbo.FamilyMemberPets
  GROUP BY Name
  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
  GROUP BY Name
  ORDER BY Name;

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:

GROUP_CONCAT(
    DISTINCT expression
    ORDER BY expression
    SEPARATOR sep
);

Method 1:

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

Step 1: create table:

CREATE TABLE t (
    v CHAR
);

Step 2: insert table:

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

Step 3: Display Records:

SELECT 
    GROUP_CONCAT(DISTINCT v
        ORDER BY v ASC
        SEPARATOR ';')
FROM t;

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:

SELECT 
    GROUP_CONCAT(country)
FROM
    customers;

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:

SELECT 
    GROUP_CONCAT(DISTINCT country)
FROM
    customers;

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:

SELECT 
    GROUP_CONCAT(DISTINCT country
        ORDER BY country)
FROM
    customers;

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:

SELECT 
    GROUP_CONCAT(DISTINCT country
        ORDER BY country
        SEPARATOR ';')
FROM
    customers;

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, 
    firstname, 
    lastname, 
    customername
FROM
    employees
        INNER JOIN
    customers ON customers.salesRepEmployeeNumber = employees.employeeNumber
ORDER BY 
	firstname, 
    lastname;

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,
    firstName,
    lastName,
    GROUP_CONCAT(DISTINCT customername
        ORDER BY customerName)
FROM
    employees
        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.

SELECT GROUP_CONCAT(
   CONCAT_WS(', ', contactLastName, contactFirstName)
   SEPARATOR ';')
FROM
    customers;

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
FROM
    table_name
WHERE
    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:

SELECT 
    GROUP_CONCAT(DISTINCT country
        SEPARATOR ';')
FROM
    customers
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.

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, 
GROUP_CONCAT( DISTINCT quality) 
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, 
GROUP_CONCAT( DISTINCT emp_id ORDER BY emp_id  SEPARATOR' _ ') 
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;

Result:

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;

Result:

There are two functions for doing this:

• CONCAT
• CONCAT_WS

Both functions work similarly but have little difference.

1. CONCAT

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, ... )

OR

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.

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

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

Output:

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

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

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.

2. CONCAT_WS

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, ... )

OR

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

Example 1:

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

Output:

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

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.

Syntax:

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.

SELECT *
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):

Discussion:

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:

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

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

Example:

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.

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.

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";

Output:

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';

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');

Output:

SELECT concat_ws('s');

Output:

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

Output:

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.

SELECT CONCAT_WS("-" , "EDUREKA", "SQL");

Output:

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');

Output:

Example 4:

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

Output:

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);

Output:

Example 6: Using CONCAT_WS() to handle NULLs:

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

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

Output:

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;

Result:

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)

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 "||".

CONTACT SQL Syntax

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;

Output:

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,
    City,
	Country,
	PostalCode,
    CONCAT(CustomerName, ' ', City, '', Country, '', PostalCode) full_address
FROM 
    customers
ORDER BY 
    CustomerName;

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

MySQL/Oracle:

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

Result:

Example 5: Oracle:

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

Result:

Example 6: SQL Server:

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

Result:

I have this table:

PersonTable

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

Example 1: I've tried the following:

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

Output:

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;

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

Syntax:

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.

Example:

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;

Output:

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

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

Output: