Multiple Ways to Concatenate Values Together in SQL Server
A common use case when working with data stored in relational databases is «gluing» values together into one single big value, which is often described as concatenating. In this tip, we’ll go over the various options you have in Microsoft SQL Server T-SQL for concatenating values.
Solution
To illustrate with an example, we’re trying to achieve the following:
If the values are stored in a table over different columns, we want to concatenate over these columns. It’s also possible to concatenate row values inside a single column (sometimes referred to as «grouped concatenation»).
However, this is not the focus of this SQL tutorial. If you’re interested in this scenario, check out the excellent tip Using FOR XML PATH and STRING_AGG() to denormalize SQL Server data.
Some examples in this tip are shown using the Adventure Works sample database. You can install it on your machine so you can execute the queries yourself to see the results.
Concatenate Values Using the Concatenation Operator
In any version of SQL Server, you can use the plus sign as a concatenation operator with the following syntax:
It doesn’t really matter if you’re using string literals or variables or columns values to concatenate strings.
Mixed Data Types
When you concatenate values together, you typically have to use a separator. This ensures your values are not «sticking together». In the example above, we used a space as a separator between the first and the last name. Even though the concatenation operator is simple in its use, it has a couple of significant drawbacks. Let’s try to mix some data types.
Because of data types precedence, the strings are being converted to integers, which of course fails. When using the plus sign, it’s meaning depends on the data types used (this is called an overloaded operator). When using strings, it concatenates values. When using integers, it adds them together (and the same goes for dates). When mixing data types, you need to make sure you have converted all columns to strings if necessary. To make our expression, we need to convert the int and datetime columns using either CONVERT or CAST.
Dealing with NULL Values
Another issue with the concatenation operator are NULL values. With the default settings, if any of the expressions you’re trying to concatenate is a NULL, the result will also be NULL. Let’s take a look at the Person table and concatenate the first name, the middle name and the last name.
Not all persons have a middle name, so some rows will return a NULL instead of a string. To solve this issue, we can use ISNULL or COALESCE:
The downside is you’ll have to apply ISNULL/COALESCE to every column or variable that can potentially contain NULLs. Another option might be to set the CONCAT_NULL_YIELDS_NULL session parameter to OFF. However, this option is deprecated and should be avoided. For more information about the SET statement, check out the tip Determining SET Options for a Current Session in SQL Server.
Concatenate Values Using the CONCAT function
In SQL Server 2012 and later, we can use the CONCAT function. With this string function, you can concatenate multiple character string values at once. Let’s reprise the example from the previous paragraph:
The beauty of the CONCAT function is that it implicitly converts all expressions to string values (if possible) within a SELECT statement. This means you don’t have to use CAST or CONVERT anymore as shown in the following example!
However, as you can see in the results, sometimes you’ll still want to use CONVERT with a specific style to get datetime values into the correct format. Another advantage of the CONCAT function is that it also converts NULLs into empty strings. So you don’t need to use ISNULL or COALESCE anymore either!
If all columns are NULL, the result is an empty string. More tips about CONCAT:
- Concatenation of Different SQL Server Data Types
- Concatenate SQL Server Columns into a String with CONCAT()
- New FORMAT and CONCAT Functions in SQL Server 2012
Concatenate Values Using CONCAT_WS
In SQL Server 2017 and later, we can use the function CONCAT_WS. The WS stands for «with separator». It basically does the same thing as the CONCAT function, but you specify a separator as well which is placed between each string value. This makes some expressions a bit easier and shorter to write, especially if there are a lot of arguments. The example of the full name from the previous paragraphs now becomes:
Use Case – Calculating a Row Hash
Let’s apply everything we learned in this tip in an example. Often, you want to know if a record has changed; meaning one or more columns have been updated. For example when loading data into a dimension table.
When you have incoming data and you compare this with the current data in a table, comparing each individual column is not efficient, especially when the table has a lot of columns. An option for a quick check is calculating a row hash. If the hash of the current row in the table is different from the incoming row, you know that at least one column has a different value. This pattern is described in the tip Additional Optimizations for Type 2 Slowly Changing Dimensions in SQL Server Integration Services — Part 3.
Calculating a hash in Transact-SQL can be done with the function HASHBYTES. To calculate the hash for an entire row, we first need to concatenate all columns together into one single value. This is where CONCAT_WS comes into the picture. Let’s calculate a hash for the Person table with the following SQL commands:
- The algorithm SHA2_256 is used to calculate the hash. The HASHBYTES function supports other algorithms, but since SQL Server 2016 all algorithms except SHA2-256 and SHA2-512 are deprecated.
- The columns AdditionalContactInfo and Demographics are left out of the example since they are of the XML data type and cannot be implicitly converted.
- Two pipe symbols are used as a separator. Any string expression can be used, it doesn’t have to be one single character.
You’ll definitely want to use a separator, otherwise you might get «collisions»: different rows who result in the same hash. For example, if you don’t use a separator, the following the rows will return the same hash:
Such collisions can easily be avoided by adding a separator. Either replace CONCAT with CONCAT_WS if you’re using SQL Server 2017 or higher, or put a separator manually between each column if you’re on an older version of SQL Server.
If you have a lot of nullable columns, a collision can often happen, especially if you use a lot of flag columns (columns with the bit data type) as in this example:
Since the concatenation results in ’10’ each time, all the rows have the same hash. However, in this example CONCAT_WS doesn’t solve the issue!
The problem here is CONCAT_WS doesn’t put a separator between two columns if they are NULL, as explained in the documentation.
To solve this, we can either use ISNULL to replace NULL with some sort of dummy value or use CONCAT and put the separator explicitly between all the columns. A dummy value is not always a good idea. For example, suppose you have an integer column that can contain any values an integer can hold. Meaning, 0 and -1 are legit values for this column. What is your dummy value going to be? If you choose 0, an update that changes a NULL value to 0 will go unnoticed. Therefore, using an explicit separator is the best option.
[SQL Basic] How to work with String Functions in SQL —My SQL CONCAT, LENGTH, SUBSTR
In this tutorial, we will learn about the functions you can use to manipulate string data easily and quickly. There are many kinds of functions for strings, and sometimes different databases such as Oracle, SQL Server, and MySQL have different methods. We will cover string data based on MySQL.
#Glossary:
��Lesson 3: SELECT / FROM / WHERE
��Lesson 4: ORDER BY
��Lesson 5: AND / OR / IN
��Lesson 6: LIKE
��Lesson 7: AS /DISTINCT / IS NULL
��Lesson 9: COUNT / SUM
��Lesson 10: AVG / MAX / MIN
��Lesson 11: GROUP BY / HAVING
��Lesson 12: CASE
��Lesson 13: INNER JOIN
��Lesson 14: OUTER LEFT JOIN / OUTER RIGHT JOIN
��Lesson 15: SUBQUERIES
��Lesson 16: DATE_FORMAT / DATEDIFF
#Table of Contents
Query 1. Combine customer first and last name columns to make a new full name column (CONCAT)
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name FROM customer;Query 2. Let’s count the length of the customer’s full name (LENGTH)
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, LENGTH(CONCAT(first_name, last_name)) AS length_name FROM customer;Query 3. Let’s print out only three characters of the customer’s name (SUBSTR)
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, SUBSTR(CONCAT(first_name, “ “, last_name), 1, 3) AS short_name FROM customer;
Q 1. Combine customer first and last name columns to make a new full name column (CONCAT)
First let’s look at the customer table.
SELECT * FROM customer;
The table shows the customer first and last names stored in different columns. You may need the full name together in more complicated queries, so it’s good to know how to connect the two into one line. And to combine the two in MySQL, you can use CONCAT .
#CONCAT
Start with specifying which column you want to see: customer ID
SELECT customer_id
We also want to see the full name, so let’s use CONCAT to connect the first name and the last name. To add a space between the two, we need to specifically put one there and highlight it with double quotations ( “ ” ). Make sure you separate all three with commas :
CONCAT(first_name, “ ”, last_name)
Rename this new column as Full Name:
AS full_name
And finally, close it with the name of the table and a semicolon. Your full and final query will look like this:
SELECT customer_id, CONCAT(first_name, “ ”, last_name) AS full_name FROM customer;
⭐️ Use F9 in SQLGate to format your query!!
You can also add in some extra text in the CONCAT query like this:
SELECT customer_id, CONCAT("Hello. I am ", first_name, “ “, last_name) AS full_name FROM customer;
Q 2. Let’s count the length of the customer’s full name (characters in the first and last name).
Sometimes it is necessary to calculate the length of a string. We’re going to use the LENGTH query to do so. This function accepts string data as an input value, calculates how many characters it has, and outputs numbers (calculates based on bytes).
#LENGTH
You can use strings or string columns directly within the LENGTH function, but for this example we will use the CONCAT query we just learned. In the previous example, the result of the CONCAT function was a string corresponding to the customer’s name (full_name) so we can use it with no problems. But if CONCAT is used for numerical calculations, you won’t be able to reproduce this query.
Here is a visual break down of what this query does:
So first set the columns you want to view in the SELECT function:
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name
And then add the LENGTH function. We want to count every character in the first and last name, not including the space we added earlier, so we’re going to rewrite our CONCAT function for LENGTH . Here’s what it should look like:
LENGTH(CONCAT(first_name, last_name))
Go ahead and rename the new column as Name Length and close the query:
AS name_length FROM customer;
Here’s what the final query looks like:
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, LENGTH(CONCAT(first_name, last_name)) AS name_length FROM customer;
Great! But why did we do that? Why do we need to know the length of customer names? We have to understand this function for our Practice Time question. You’ll see it pop up again later. Let’s keep going for now
Q 3. Let’s print out only three characters of the customer’s name (first three and last three).
The SUBSTR function is used to extract only a part of the full string. It also allows you to set a starting position and specify how many characters you want to extract.
#SUBSTR
Let’s keep building on our earlier query. Start with SELECT :
SELECT customer_id, CONCAT(first_name, “ “, last_name)
Don’t forget to rename the new column:
AS full_name
Then plug in SUBSTR and inside of it, duplicate your CONCAT function. Here, we have two new factors to think about. First, we need to state the start position. Since we want it to start at the very beginning, we will use the number 1 . Next we need to state how many characters of the name we wanted printed. Let’s say only 3 characters:
SUBSTR(CONCAT(first_name, “ “, last_name), 1, 3)
Here is a visual demonstration of what SUBTR does:
Rename this column as well:
AS short_name
Close it with a FROM function:
FROM customer;
Your final query should look like this:
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, SUBSTR(CONCAT(first_name, “ “, last_name), 1, 3) AS short_name FROM customer;
So now we only see the first 3 characters of every name! MARY SMITH has become just MAR. �� Nice!
Let’s say we want to print only the last 3 characters of a string. How would we go about it? This is a bit tough. Since the length of all the names are different, we can’t count the exact position. But what we can do is go backwards! Just tell SQLGate you want to go back 3 positions on all the names and start there. We can do this by writing negative 3 ( -3 ). It should look like this:
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, SUBSTR(CONCAT(first_name, “ “, last_name), 1, 3) AS short_name FROM customer;
�� You can set your start position at the front of the string by using positive numbers, or set it at the back of the string by using negative numbers.
#Practice Time
Print a list of customer names and their censored emails (using CONCAT, SUBSTR, REPEAT, LENGTH)
When dealing with customer’s personal information, such as name, address, and phone number, we need handle it without exposing it. Using the functions we learned thus far, let’s use the customer table to replace the customer’s email with a string that can’t be identified.
We’re going to use CONCAT and SUBSTR to censor the email. First, use CONCAT to get the customers’ full name. Then use CONCAT and SUBSTR together to retrieve the email, use REPEAT and INSTR to censor it and use AS to rename the column.
��REPEAT: repeat a string as many times as desired
��INSTR: returns position of first occurrence of a string in another string
Here’s what the result looks like:
Feeling a bit confused? Let’s break it down:
Start with your SELECT function and follow it up with the first column you want to view, the Customer ID column:
SELECT customer_id
Use CONCAT to combine the first and last name of customers, and rename the new column as full name:
CONCAT(first_name, “ “, last_name) AS full_name
Use CONCAT again to create the secret email column using these steps:
- Print the first 3 letters of a customers email using SUBSTR
(SUBSTR(email, 1, 3) - With INSTR we will identify a string ( email ) and specify a certain character ( @ )as the starting position we want in the string.
INSTR(email, ‘@’) - Now since we want to censor the email, we need to put asterisks ( * ) in to fill the remaining length (characters) after the original 3 we printed. Because that length is different for every email, we need to repeat it as many times as necessary, so let’s use REPEAT . We’re also going to subtract 1 ( -1 ) for the @ symbol, and subtract 3 ( -3 ) for the 3 printed text of the email, so that information doesn’t get censored.
REPEAT(‘*’, INSTR(email)-1-3) - Add the domain name to the string and close the CONCAT function:
‘@sakilacustomer.org’) - Rename the new column and write the name of the table:
AS secret_email FROM customer;
When you run the query (F5) you will get the first 3 characters of their email printed, followed by asterisks that censor the rest of the personal information! Here is the full query out together:
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, CONCAT(SUBSTR(email, 1, 3), REPEAT(‘*’, INSTR(email, ‘@’)-1–3), ‘@sakilacustomer.org’) AS secret_email FROM customer;
It may seem somewhat complex, but it’s not difficult to define each string you want to see at once and then combine it with the CONCAT function. Though we only demonstrated a commonly used practice of these functions, there are many other ways to use them. Experiment with them to find the right
Using SQL CONCAT Function to Concatenate Two or More Strings
Summary: in this tutorial, you will learn how to use the SQL CONCAT function to concatenate two or more strings into a single string.
Introduction to SQL CONCAT function
The SQL CONCAT function concatenates two or more strings into one string. The following illustrates the syntax of the CONCAT function:
To concatenate strings, you pass the strings as a list comma-separated arguments to the function.
The CONCAT function returns a string which is the combination of the input strings. It returns NULL if one of the argument is NULL . (To handle NULL values more effectively you can use the IS NULL operator or COALESCE and NULLIF functions.)
Most relational database systems support the CONCAT function with some differences among them. For example, MySQL CONCAT function allows you to concatenate more than two strings whereas Oracle CONCAT function concatenates exactly two strings.
Besides using the CONCAT function, you can use the concatenation operator e.g., in Oracle and PostgreSQL you can use the || operator to concatenate two or more strings. And in Microsoft SQL Server, you use the + operator.
SQL CONCAT examples
The following statement uses the CONCAT function to concatenate two strings:
The following statement uses the CONCAT function to return the full name of the employees by concatenating the first name, space, and last name.
If you are using Oracle database, you have to apply the CONCAT function twice the achieve the same result. See the following query:
The inner CONCAT function concatenates the first name with space, and the outer CONCAT function concatenates the result of the inner CONCAT function with the last name.
It will be much cleaner if you use the concatenation operator in Oracle (and also PostgreSQL).
In Microsoft SQL Server, you would use the following query:
If you are using MySQL or PostgreSQL, you can use the CONCAT_WS function to concatenate strings with a separator.
For example, you can use the CONCAT_WS function to construct the full name of the employee as follows:
In this tutorial, you have learned how to use the SQL CONCAT function to concatenate two or more strings into a single string.
Конкатенация в SQL-запросах
Конкатенацией называется операция, которая позволяет соединить несколько текстовых строк в одну.
В работе с базами данных конкатенация используется, когда нужно соединить значения нескольких полей или присоединить к значениям полей текст.
В SQL используется три вида конкатенации:
- Простая конкатенация;
- Конкатенация с разделителем;
- Групповая конктенация.
В различных «диалектах» языка SQL функции, реализующие конкатенацию, несколько различаются своим синтаксисом. Но принципы их работы одинаковы. Эти принципы будут рассмотрены на примере функций СУБД MySQL.
Простая конкатенация
Простая конкатенация в СУБД MySQL выполняется с помощью встроенной функции CONCAT() имеющей следующий синтаксис:
CONCAT(строка1, строка2, ……строкаN)
Строки, являющиеся аргументами функции, могут быть именами полей, а могут быть текстовыми строками в кавычках.
В качестве примера рассмотрим запрос:
SELECT concat(«Абитуриент «, fio, » возрастом «, age, » рекомендован(a) к зачислению») as text1 FROM abiturient;
Результатом запроса будет собранный из кусочков текст:
Конкатенация с разделителем
Этот вид конкатенации в MySQL выполняется с помощью функции CONCAT_WS(), имеющей следующий синтаксис:
CONCAT_WS(‘символ_разделитель’, строка1, строка2…)
В этом случае между соединяемыми строками будет установлен символ-разделитель.
Например, в данном запросе в качестве разделителя используется запятая:
SELECT concat_ws(‘,’, fio,age,gender) as text1 FROM abiturient;
Если в качестве разделителя использовать пустую строку, то результат будет полностью совпадать с результатом простой конкатенации. Если разделителем должен быть символ кавычки ‘, то его нужно экранировать обратным слешем – ‘\».
SELECT concat_ws(‘\», fio,age,gender) as text1 FROM abiturient;
Групповая конкатенация
Пусть даны две таблицы. В одной хранятся личные данные абитурентов, а в другой – поданные заявления. Считается, что каждый абитуриент может подать несколько заявлений на разные специальности. Тогда, чтобы увидеть список абитуриентов и список специальностей, на которые они подали заявления, нужно построить следующий запрос:
SELECT abiturient.idabiturient, abiturient.fio, application.namespec FROM abiturient JOIN application ON abiturient.idabiturient=application.idabiturient;
Результат запроса будет таким:
Теперь усложним задачу. Пусть необходимо собрать специальности по каждому абитуриенту в группу и записать их через запятую. То есть в результате должна получиться такая таблица:
Записанные через запятую названия специальностей, относящиеся к данному абитуриенту, — это и есть результат групповой конкатенации.
Для выполнения групповой конкатенации в MySQL используется инструкция GROUP BY и функция GROUP_CONCAT ().
GROUP_CONCAT([DISTINCT] строка1, строка2. [ORDER BY имя_поля или выражение [ASC | DESC]] [SEPARATOR ‘символ_разделитель’])
Необязательная инструкция DISTINCT позволяет удалить из списка совпадающие строки. Инструкция ORDER BY позволяет упорядочить строки по какому-либо полю или выражению. Упорядочивание по возрастанию определяется инструкцией ASC, по убыванию –DESC. Инструкция SEPARATOR позволяет задать символ, который будет разделять строки. По умолчанию разделителем является запятая.
Все запросы с групповой конкатенацией являются запросами группового типа. Функция GROUP_CONCAT() может быть отнесена к групповым операциям наряду с COUNT(), SUM(),MIN(),MAX(), AVG().
Таким образом, для получения списка специальностей по каждому абитуриенту нужно выполнить следующий запрос: