Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Write More Compact SQL Server Code Using New Features - Part 2


By:   |   Last Updated: 2018-12-03   |   Comments (2)   |   Related Tips: More > T-SQL

Problem

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.

Solution

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:

'psychology,physics,chemistry,astrology,mathematics,biology,geography,computer science,history'

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.

query results

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:

query results

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:

query results

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:

query results

However, we have to write a report that will show the students addresses in the following format:

query results

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:

query results

Conclusion

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.

Next Steps

Please find additional information below:



Last Updated: 2018-12-03


next webcast button


next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, December 06, 2018 - 4:55:13 AM - Thomas Franz Back To Top

another problem with the code:

Prior to SQL 2017 there was already a CONCAT() function. In your example you do not use it, but concate the fields with + which would result in a NULL string, when one of the address fields is NULL.

The main difference between CONCAT() and CONCAT_WS() is, that you do not need to specify the delimiter between every field, which saves you some boring work. Drawback: you can't use different delimiters.

Another difference between both functions is the behavior, when a field is NULL. Both functions would ignore it (set it internal to ''), but CONCAT_WS would skip the delimiter too.

So CONCAT('a', ',', NULL, ',', 'c') would return 'a,,c', while CONCAT_WS(',', 'a', NULL, 'c') would return 'a,c', which could of course be intended / better (think of concating first, middle and lastname and the double space problem when there is no middle name).

BTW: to have the same result with CONCAT you can use CONCAT('a' + ',', NULL + ',', 'c') - since NULL + ',' would result in NULL and NULL will be ignored by CONCAT() it would return 'a,c' in this case.


Monday, December 03, 2018 - 12:11:45 PM - GWB Back To Top

†A couple of problem areas:

  1. String_split doesn't guarantee results in any particular order.† that can be a problem depending on the application
  2. concat_ws drops null columns from the output.† that could be a problem e.g. when generating CSV files

Learn more about SQL Server tools