By: Sergey Gigoyan | Updated: 2018-12-03 | Comments (2) | T-SQL
In the previous article, we have discussed how the new features of SQL Server allows us to rewrite the old code in a more compact way. In this article, we will continue to demonstrate some other new features of SQL Server 2016/2017, allowing us to write shorter code for the same tasks.
In this article, we will demonstrate the usage of two string functions STRING_SPLIT and CONCAT_WS introduced in SQL Server 2016 and 2017 correspondingly and will compare the old code written without using these functions with the new code.
Creating the test environment
In the previous article, we created the TestDB database with three tables and data: Student, Course, and StudentCourse table that represents the mapping between Student and Course tables:
USE master GO --Database CREATE DATABASE TestDB GO USE TestDB GO --Tables CREATE TABLE Student ( StudentID INT NOT NULL, LoginName NVARCHAR(50), PRIMARY KEY (StudentID) ) GO CREATE TABLE Course ( CourseID INT NOT NULL, CourseName NVARCHAR(50) UNIQUE, PRIMARY KEY (CourseID) ) GO CREATE TABLE StudentCourse ( StudentCourseID INT NOT NULL IDENTITY(1,1), StudentID INT, CourseID INT, PRIMARY KEY (StudentCourseID), CONSTRAINT UC_StudentID_CourseID UNIQUE(StudentID,CourseID), ) GO --Filling data INSERT INTO Student(StudentID, LoginName) VALUES (1, ' [email protected] '), (2, '[email protected] '), (3, ' [email protected]'), (4, '[email protected]') INSERT INTO Course(CourseID, CourseName) VALUES (1, 'psychology'), (2, 'physics'), (3, 'chemistry'), (4, 'astrology'), (5, 'mathematics'), (6, 'biology'), (7, 'geography') INSERT INTO StudentCourse(StudentID, CourseID) VALUES (1, 1), (1, 3), (1, 5), (2, 5), (3, 1), (3, 2), (3, 3), (3, 4), (3, 5), (3, 6), (3, 7), (4, 2), (4, 4), (4, 5)
In this article, we will also use this environment for testing the remainder of the examples.
Build a Single-Column SQL Server Table from Separated Values in a String
Many developers have faced a situation where they needed to separate values in a string into several rows. Let's imagine, that we regularly receive updated, comma-separated list of subjects and we need to update the data in the 'Course' table.
Let's assume that we have been provided with the following string as a list of subjects to compare them with the subjects in the 'Course' table and add the new subjects to the table:
Having a single-column table, like in the picture below, instead of the comma-separated list will allow us to compare and merge data into the existing table. So, we need to convert the comma-separated string to a single-column table.
Earlier SQL Server Versions - Separate Delimited String into Rows Using a Loop
Before SQL Server 2016, we can solve this task in the following way:
USE TestDB GO --Comma-separated string into table in older versions of SQL Server DECLARE @Subject TABLE ( SubjectName NVARCHAR(50) ) DECLARE @CurrentValue NVARCHAR(50) DECLARE @str NVARCHAR(MAX)='psychology,physics,chemistry,astrology,mathematics,biology,geography,computer science,history' WHILE CHARINDEX(',',@str)>0 BEGIN SET @CurrentValue = (SELECT SUBSTRING(@str,1,CHARINDEX(',',@str)-1) ) INSERT INTO @Subject(SubjectName) VALUES(@CurrentValue) SET @str=(SELECT RIGHT(@str, LEN(@str)-LEN(@CurrentValue )-1 )) END INSERT INTO @Subject(SubjectName) VALUES(@str) SELECT * FROM @Subject
We declare a table variable and inside the WHILE loop, we separate each subject from the string by using several string functions and then insert these values into the table variable.
SQL Server 2016 and Beyond - Separate Delimited String into Rows Using STRING_SPLIT
SQL Server 2016 introduced the function STRING_SPLIT. This allows us to solve the same task by writing a single line of code. This function accepts two parameters: string and separator and returns a table with one column called VALUE, where values in the VALUE column are fragments of the string defined by the separator. Using this function, we can rewrite the code above in the following way:
USE TestDB GO --Comma-separated string into table in SQL Server 2016 and higher versions SELECT VALUE AS SubjectName FROM STRING_SPLIT('psychology,physics,chemistry,astrology,mathematics,biology,geography,computer science,history', ',')
As we can see the code is much shorter, but the result is the same:
We can then use a MERGE statement to insert the new subjects into the Course table as follows:
USE TestDB GO DECLARE @MaxCourseID INT SET @MaxCourseID = (SELECT MAX(CourseID) FROM Course) MERGE Course AS target USING (SELECT ROW_NUMBER() OVER (ORDER BY VALUE) + @MaxCourseID AS SubjectID, VALUE AS SubjectName FROM STRING_SPLIT('psychology,physics,chemistry,astrology,mathematics,biology,geography,computer science,history', ',') ) AS source ON target.CourseName=source.SubjectName WHEN NOT MATCHED BY target THEN INSERT (CourseID, CourseName) VALUES(source.SubjectID, source.SubjectName); SELECT * FROM Course ORDER BY CourseID
We can see that two new courses, (history and computer science) have been added to the table:
Concatenating Several Columns into one Column in SQL Server
For each row, we need to group values from the given columns into one string.
We have a table called StudentAddress for storing students addresses:
USE TestDB GO CREATE TABLE StudentAddress ( StudentAddressID INT NOT NULL IDENTITY(1,1), StudentID INT REFERENCES Student(StudentID), AppartmentNum NVARCHAR(25), Street NVARCHAR(50), City NVARCHAR(50), [State] NVARCHAR(50), PostalCode NVARCHAR(25), Country NVARCHAR(50), PRIMARY KEY (StudentAddressID) ) GO INSERT INTO StudentAddress(StudentID,AppartmentNum,Street,City,[State], PostalCode, Country) VALUES (1, 202, 'Queen Street', 'Albany','NH','03818', 'USA'), (2, 54, 'Teryan Street', 'Yerevan','Yerevan','0204', 'Armenia'), (3, 97, 'King Str', 'Albuquerque','NM','87101', 'USA'), (4, 299, 'Woodbin ave', 'Toronto','ON','M4C5K7', 'Canada') GO --Students' addresses SELECT s.LoginName, AppartmentNum,Street,City,[State], PostalCode, Country FROM Student s INNER JOIN StudentAddress a ON s.StudentID=a.StudentID
In this table, we have a separate column for each attribute of the address:
However, we have to write a report that will show the students addresses in the following format:
Prior SQL Server Versions - Concatenating Several Strings with +
In older versions of SQL Server, we can achieve this task with the following code:
USE TestDB GO --Students addresses in a one column in older versions of SQL Server SELECT s.LoginName, AppartmentNum +',' + Street + ',' + City + ',' + [State] + ',' + PostalCode + ',' + Country AS StudentAddress FROM Student s INNER JOIN StudentAddress a ON s.StudentID=a.StudentID
SQL Server 2017 and Beyond - Concatenating Several Strings with CONCAT_WS
In SQL Server 2017, there is a CONCAT_WS function allowing us to write the same code in a bit more of a compact way. This function accepts the separator as the first parameter and two or more string arguments. As a result, it returns the concatenated string of the arguments divided by the separator. In our case, the separator is a comma and the arguments are the column values:
--Students addresses in one a column in SQL Server 2017 SELECT s.LoginName, CONCAT_WS(',',AppartmentNum, Street, City, [State], PostalCode, Country) AS StudentAddress FROM Student s INNER JOIN StudentAddress a ON s.StudentID=a.StudentID
As a result, we will have the same output:
In conclusion, the STRING_SPLIT and CONCAT_WS functions can add flexibility and compactness to your code. They are very useful when dealing with such tasks as converting a comma-separated string (or a string separated by another symbol) to a table or joining several string values into one list divided by a separator.
Please find additional information below:
- T-SQL Enhancements in SQL Server 2017
- SQL Server 2016 STRING_SPLIT Function
- Solve old problems with SQL Serverís new STRING_AGG and STRING_SPLIT functions
- Parsing string data with the new SQL Server 2016 STRING_SPLIT function
- Dealing with the single-character delimiter in SQL Server's STRING_SPLIT function
Last Updated: 2018-12-03
About the author
View all my tips