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

Splitting a string by a delimiter in SQL Server involves breaking a single text string into smaller parts based on a chosen character. This is often done using functions like STRING_SPLIT, which processes the input string and outputs the parts as separate rows.

However, STRING_SPLIT does not maintain the order of the parts, which can be an issue if the order matters. It also doesn’t support multi-character separators and might not handle extra separators at the start or end of the string correctly. In these situations, custom functions or other methods might be needed to split the string accurately.

In this article, we will discuss several ways to split a delimited string value through multiple methods, including:

• Using the STRING_SPLIT function to split the string
• Creating a user-defined table-valued function to split the string
• Using XQuery to split the string value and transform a delimited string into XML

First, 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 fields `ID` and `student_name`, where the string includes delimiter characters. 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 whether the data has been inserted into the table by using the following code:

SELECT * FROM student

1. Use STRING_SPLIT Function to Split the String

An easy way to split strings in SQL Server is by using the STRING_SPLIT function. This feature breaks a string into separate values based on a chosen single-character separator and returns them as rows in a table. It’s simple to use, but remember that STRING_SPLIT doesn’t keep the order of the parts, so you might need to sort them if the order is important. This method is great for handling basic separated data without needing complex queries.

The STRING_SPLIT function takes a string containing delimited substrings and uses one character as the delimiter or separator. It outputs a single-column table where each row contains a substring. The name of the output column is Value. This function accepts two parameters: the first is a string, and the second is a delimiter character or separator based on which the string is split. The output column is named “Value” as shown 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 associated with a compatibility level, allowing it to behave consistently with the specific SQL Server version it runs on.

Now, we will call the “STRING_SPLIT” function to split a string delimited by commas. Since the compatibility level was less than 130, the following error was raised: “Invalid object name ‘SPLIT_STRING'”.

Thus, we need to set the database compatibility level to 130 or higher. Follow these steps to set the compatibility level of the database:

• First, set the database to “single_user access mode” using the following code.

ALTER DATABASE <[database_name]> SET SINGLE_USER

• Next, change the database’s compatibility level using the following code.

ALTER DATABASE <[database_name]> SET COMPATIBILITY_LEVEL = 130

• Switch the database back to multi-user access mode 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, ',')

The output for this query will be:

2. To Split the String, Create a User-Defined Table-Valued Function

When functions like STRING_SPLIT can’t handle complex separators or keep the order of items, creating a custom table-valued function can help. This method gives you control over how you split the string, supports multi-character separators, keeps items in order, and manages issues like leading or trailing separators. Using a custom function ensures the results meet your needs, making it a flexible option for splitting strings in SQL Server. 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 the split function to break the string by the 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 look like this:

3. Use XQuery to Split the String Value and Transform a Delimited String into XML

Turning a delimited string into XML using XQuery is a structured way to work with the data. By changing the string into XML, each part between the delimiters becomes a separate XML element. This method gives better control over complex delimiters, supports multi-character delimiters, and keeps the order of elements. It is a strong option for precise string splitting in SQL Server. A string can be split using the following XML approach.

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:

To view the entire XML file, click on the link provided. Once opened, the code will look like this:

Next, the XML string should be processed further. Finally, we will use “XQuery” 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: