How to Split a String by a Delimited Char in SQL Server?

In this article, we will discuss several ways to split the delimited string value. It can be achieved using multiple methods including.

• Use of STRING_SPLIT function to split the string
• Create a user-defined table-valued function to split the string,
• Use XQuery to split the string value and transform a delimited string into XML

First of all, we need to create a table and insert data into it which will be used in all three methods. The table should contain a single row with field id and string with delimiter characters in it. Create a table named “student” using the following code.

CREATE TABLE student
  (
     ID INT IDENTITY (1, 1),
     student_name VARCHAR(MAX)
  )

Insert student names separated by commas in a single row by executing the following code.

INSERT INTO student (student_name)
VALUES ('Monroy,Montanez,Marolahakis,Negley,Albright,Garofolo,Pereira,Johnson,Wagner,Conrad')

Verify if data has been inserted into the table or not using the following code.

select * from student

Method 1: Use STRING_SPLIT function to split the string

In SQL Server 2016, “STRING_SPLIT” function was introduced which can be used with compatibility level 130 and above. If you use the 2016 SQL Server version or higher you can use this build-in function.

Furthermore “STRING_SPLIT” inputs a string that has delimited sub-strings and inputs one character to use as the delimiter or separator. The function outputs a single-column table whose rows contain the sub-strings. The name of the output column is “Value”. This function gets two parameters. The first parameter is a string and the second one is delimiter character or separator based on which we have to split the string. The output contains a single-column table in which substrings are present. This output column is named “Value” as we can see in the figure below. Moreover, the “STRING SPLIT” table_valued function returns an empty table if the input string is NULL.

Compatibility level of the database:

Each database is connected with a compatibility level. It enables the database’s behavior to be compatible with the particular SQL Server version it runs on.

Now we will call “string_split” function to split string delimited by commas. But the compatibility level was less than 130 hence following error was raised. “Invalid object name ‘SPLIT_STRING'”

Thus we need to set the database compatibility level to 130 or higher. So we will follow these step to set the compatibility level of the database.

• First of all set database to “single_user_access_mode” by using the following code.

ALTER DATABASE <[database_name]> SET SINGLE_USER

• Secondly, change the compatibility level of the database by using the following code.

ALTER DATABASE <[database_name]> SET COMPATIBILITY_LEVEL = 130

• Put the database back to multi-user access mode by using the following code.

ALTER DATABASE <[database_name]> SET MULTI_USER

USE [master]
GO
ALTER DATABASE [bridge_centrality] SET SINGLE_USER
ALTER DATABASE [bridge_centrality] SET COMPATIBILITY_LEVEL = 130
ALTER DATABASE [bridge_centrality] SET MULTI_USER
GO

The output will be:

Now run this code to get the required result.

DECLARE @string_value VARCHAR(MAX) ;
SET @string_value='Monroy,Montanez,Marolahakis,Negley,Albright,Garofolo,Pereira,Johnson,Wagner,Conrad'
SELECT * FROM  STRING_SPLIT (@string_value, ',')

Output for this query will be:

Method 2: To split the string, create a user-defined table-valued function

Certainly, this traditional method is supported by all versions of SQL Server. In this technique we will create user-defined function to split the string by delimited character using “SUBSTRING” function, “CHARINDEX” and while loop. This function can be used to add data to the output table as its return type is “table”.

CREATE FUNCTION [dbo].[split_string]
(
    @string_value NVARCHAR(MAX),
    @delimiter_character CHAR(1)
)
RETURNS @result_set TABLE(splited_data NVARCHAR(MAX)
)
BEGIN
    DECLARE @start_position INT,
            @ending_position INT
    SELECT @start_position = 1,
            @ending_position = CHARINDEX(@delimiter_character, @string_value)
    WHILE @start_position < LEN(@string_value) + 1
            BEGIN
        IF @ending_position = 0 
           SET @ending_position = LEN(@string_value) + 1
        INSERT INTO @result_set (splited_data) 
        VALUES(SUBSTRING(@string_value, @start_position, @ending_position - @start_position))
        SET @start_position = @ending_position + 1
        SET @ending_position = CHARINDEX(@delimiter_character, @string_value, @start_position)
    END
    RETURN
END

Now execute the script below to call a split function to split string by delimiter character.

DECLARE @student_name VARCHAR(MAX);
DECLARE @delimiter CHAR(1);
SET @delimiter=','
SET @student_name =(SELECT student_name FROM   student)
SELECT * FROM dbo.split_string(@student_name, @delimiter)

The result set will be like this.

Method 3: Use XQuery to split the string value and transform a delimited string into XML

As user-defined functions are resource exhaustive so we must avoid these functions. Another option is built-in “string_split” function but this function can be used for database for which compatibility level is 130 or higher. So here comes another solution to solve this difficult task. A string can be splitted using the following XML.

DECLARE @xml_value AS XML,
        @string_value AS VARCHAR(2000),
        @delimiter_value AS VARCHAR(15)
SET @string_value=(SELECT student_name FROM student)
SET @delimiter_value =','
SET @xml_value = Cast(( '<studentname>'
                  + Replace(@string_value, @delimiter_value, '</studentname><studentname>')
                  + '</studentname>' ) AS XML)
SELECT @xml_value

The output for this query will be:

If you want to view the whole XML file. Click on the link. Once you have clicked the link code will look like this.

Now XML string should be processed further. Finally, we will use “x-Query” to query from the XML.

DECLARE @xml_value AS XML,
        @string_value AS VARCHAR(2000),
        @delimiter_value AS VARCHAR(15)
SET @string_value=(SELECT student_name FROM student)
SET @delimiter_value =','
SET @xml_value = Cast(( '<studentname>'
                  + Replace(@string_value, @delimiter_value, '</studentname><studentname>')
                  + '</studentname>' ) AS XML)
SELECT   x.m.query('.').value('.', 'VARCHAR(15)') AS VALUE
FROM   @xml_value.nodes('/studentname') AS x(m)

The output will be like this: