SQL Server String Split: A Comprehensive Guide for Devs

Greetings, Devs! In this article, we’ll be discussing everything you need to know about SQL Server String Split. From its purpose to its implementation, we’ve got you covered. Let’s delve into the world of SQL Server and string splitting!

What is SQL Server String Split?

SQL Server String Split is a built-in function in SQL Server that allows you to split a string into multiple substrings based on a specified delimiter. It returns a table with the splitted substrings as its rows. This function is commonly used in data manipulation and analysis.

Before we dive deep into how to use this function, let’s take a look at its syntax:

Element	Description
string_expression	The string to be splitted
separator	The character or string to be used as delimiter
index	An optional argument that specifies the position of the substring to be returned

Now that we know the syntax, let’s proceed on how to use this function in different scenarios.

Using SQL Server String Split: Basic Examples

In this section, we’ll be discussing the basic usage of SQL Server String Split. We’ll be using simple string inputs and delimiter characters for this scenario.

Scenario 1: Splitting a Comma-Separated String

Suppose you have the following comma-separated string:

Sample Input
‘apple,banana,orange,grape’

To split this string into separate substrings, you can use the following SQL query:

 SELECT *FROM STRING_SPLIT('apple,banana,orange,grape', ',')

The result of this query will be:

Values
apple
banana
orange
grape

As you can see, the function splits the input string into four separate substrings based on the comma delimiter. The result is a table with each substring as its row.

Scenario 2: Splitting a Space-Separated String

Now, let’s try splitting a space-separated string:

Sample Input
‘hello world this is SQL Server’

You can split this string using the same SQL query as before:

 SELECT *FROM STRING_SPLIT('hello world this is SQL Server', ' ')

The result of this query will be:

Values
hello
world
this
is
SQL
Server

As you can see, the function splits the input string into six separate substrings based on the space delimiter.

More Advanced Examples of SQL Server String Split

Now that you’ve seen the basic usage of SQL Server String Split, let’s explore more advanced scenarios where this function can be useful.

Scenario 3: Splitting a String with a Custom Delimiter

In the previous examples, we used comma and space as delimiters. However, you can also use custom delimiters for your input string.

Suppose you have the following string with semicolon as delimiter:

Sample Input
‘John;Doe;35;Male’

To split this string, you can use the same SQL query as before but with semicolon as the delimiter:

 SELECT *FROM STRING_SPLIT('John;Doe;35;Male', ';')

The result of this query will be:

Values
John
Doe
35
Male

As you can see, the function splits the input string into four separate substrings based on the semicolon delimiter.

Scenario 4: Splitting a String and Selecting a Specific Substring

Sometimes, you may want to select a specific substring from the splitted string. You can do this by specifying the index of the substring in the SQL query.

For example, suppose you have the following comma-separated string:

Sample Input
‘apple,banana,orange,grape’

To select the second substring from this string (which is ‘banana’), you can use the following SQL query:

 SELECT valueFROM STRING_SPLIT('apple,banana,orange,grape', ',') WHERE [key] = 2

The result of this query will be:

Value
banana

As you can see, the function selects the second substring from the splitted string which corresponds to the index specified in the SQL query.

Scenario 5: Splitting Multiple Strings at Once

You can also split multiple strings at once using SQL Server String Split. You can do this by using a table variable and a loop.

Suppose you have the following table with three rows:

ID	String
1	‘apple,banana,orange,grape’
2	‘hello world this is SQL Server’
3	‘John;Doe;35;Male’

To split each string in this table, you can use the following SQL query:

 DECLARE @table TABLE (ID INT, Value VARCHAR(100)) INSERT INTO @table (ID, Value) SELECT ID, String FROM SampleTable DECLARE @i INT = 1 WHILE @i <= (SELECT COUNT(*) FROM @table) BEGINSELECT valueFROM STRING_SPLIT((SELECT Value FROM @table WHERE ID = @i), ',')SET @i = @i + 1 END

The result of this query will be:

Value
apple
banana
orange
grape
hello
world
this
is
SQL
Server
John
Doe
35
Male

As you can see, the function splits each string in the table and returns each substring as a row in the resulting table.

FAQ

1. What is the maximum length of the input string in SQL Server String Split?

The maximum length of the input string is 8000 characters. If your input string is longer than 8000 characters, you'll need to split it into smaller chunks before using SQL Server String Split.

2. Can I specify multiple delimiters in SQL Server String Split?

No, you can only specify a single delimiter in SQL Server String Split. If you want to split a string based on multiple delimiters, you'll need to use a different function or a custom solution.

3. Can I use SQL Server String Split in older versions of SQL Server?

No, SQL Server String Split is only available in SQL Server 2016 and later versions.

4. Can I use SQL Server String Split in Azure SQL Database?

Yes, SQL Server String Split is supported in Azure SQL Database. However, the input string can be no longer than 4000 characters.

5. Can I use SQL Server String Split in stored procedures and functions?

Yes, you can use SQL Server String Split in stored procedures and functions. In fact, it's a common function used in data manipulation and analysis scenarios.

Conclusion

SQL Server String Split is a powerful and versatile function that allows you to split a string into multiple substrings based on a specified delimiter. Whether you're working with simple or complex input strings, SQL Server String Split can help you manipulate and analyze your data more effectively. We hope this comprehensive guide has been helpful to you and your SQL Server endeavours.