Write More Compact SQL Server Code Using New Features

By: Sergey Gigoyan | Updated: 2018-12-03 | Comments (2) | Related: > TSQL

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.

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:

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:

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-12-03

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