SQL Server Split String by Delimiter

Hey Dev, welcome to this journal article where we are going to explore how to split a string by delimiter in SQL Server. In this article, we will cover all the possible scenarios where you may need to split a string and some of the best practices to follow while doing so.

Understanding Delimiters

Before we dive deep into splitting the string, let’s first understand what a delimiter is. A delimiter is a character or set of characters that are used to separate a string into several parts. For example, in the string “apple,banana,mango” the comma (,) is the delimiter that separates the fruits.

There are various delimiters that are commonly used, such as a comma, a semicolon, a colon, a hyphen, a space or a tab. However, in some cases, you may need to use a custom delimiter that is not a standard one.

Common Delimiters

Here is a list of some common delimiters that are used in different scenarios:

Delimiter	Use Case
,	CSV files
;	European CSV files
:	Time format (HH:MM:SS)
–	Date format (YYYY-MM-DD)
|	Custom delimiter

Splitting a String Using STRING_SPLIT Function

The easiest way to split a string by delimiter in SQL Server is to use the STRING_SPLIT function. This function splits a string into a table of substrings based on a specified delimiter.

Here is the syntax of the STRING_SPLIT function:

SELECT valueFROM STRING_SPLIT(string, delimiter)

Parameters of STRING_SPLIT Function

The STRING_SPLIT function accepts two parameters:

• string: The string that you want to split into substrings.
• delimiter: The delimiter that you want to use to split the string.

Example of STRING_SPLIT Function

Let’s say we have a string of names separated by a comma and we want to split them into individual names. Here is how we can do it using the STRING_SPLIT function:

SELECT valueFROM STRING_SPLIT('John,Doe,Jane,Doe', ',')

The above query will return the following result:

value
John
Doe
Jane
Doe

Splitting a String Using XML Method

In some cases, you may not be able to use the STRING_SPLIT function due to compatibility issues. In such scenarios, you can use the XML method to split the string.

How the XML Method Works

The XML method works by converting the string into an XML document and then querying the document to extract the substrings. Here is the general syntax of the XML method:

DECLARE @xml XMLSET @xml = '' + REPLACE(string,',','') + ''SELECTT.c.value('.','varchar(100)') AS valueFROM @xml.nodes('/root/s') T(c)

Example of XML Method

Let’s say we have a string of numbers separated by a colon and we want to split them into individual numbers. Here is how we can do it using the XML method:

DECLARE @string VARCHAR(100)SET @string = '10:20:30:40:50'DECLARE @xml XMLSET @xml = '' + REPLACE(@string,':','') + ''SELECTT.c.value('.','varchar(100)') AS valueFROM @xml.nodes('/root/s') T(c)

The above query will return the following result:

value
10
20
30
40
50

Splitting a String Using User-Defined Function

If you need to split a string frequently and want to avoid writing the same code repeatedly, you can create a user-defined function to split the string. This will make your code more concise and readable.

Here is the code to create a user-defined function to split a string:

CREATE FUNCTION SplitString (@string NVARCHAR(MAX),@delimiter CHAR(1))RETURNS @output TABLE (value NVARCHAR(MAX))ASBEGINDECLARE @start INT, @end INTSELECT @start = 1, @end = CHARINDEX(@delimiter, @string)WHILE @start <= LEN(@string) + 1BEGINIF @end = 0SET @end = LEN(@string) + 1INSERT INTO @output (value)VALUES(SUBSTRING(@string, @start, @end - @start))SET @start = @end + 1SET @end = CHARINDEX(@delimiter, @string, @end + 1)ENDRETURNEND

Once you have created the function, you can use it to split the string like this:

SELECT valueFROM SplitString('John,Doe,Jane,Doe', ',')

The above query will return the same result as the STRING_SPLIT function.

FAQ

What is the maximum length of the string that can be split using STRING_SPLIT function?

The maximum length of the string that can be split using the STRING_SPLIT function is 4,000 characters. If the string is longer than 4,000 characters, it will be truncated.

Can I use a custom delimiter with the STRING_SPLIT function?

Yes, you can use any single character as a delimiter with the STRING_SPLIT function.

Can I split a string based on multiple delimiters?

No, the STRING_SPLIT function can only split a string based on a single delimiter. If you need to split a string based on multiple delimiters, you will need to use a user-defined function or the XML method.

Is the XML method slower than the STRING_SPLIT function?

Yes, the XML method is slower than the STRING_SPLIT function. However, the difference in performance is negligible for small datasets.

Can I split a string using regular expressions in SQL Server?

No, SQL Server does not support regular expressions natively. However, there are some third-party tools that you can use to achieve this.

Conclusion

Splitting a string by delimiter is a common requirement in many SQL Server projects. In this article, we learned how to split a string using the STRING_SPLIT function, the XML method, and a user-defined function. We also discussed some of the best practices to follow while doing so.

With these techniques, you can efficiently split a string and make your SQL code more concise and readable.