STRING_SPLIT Function in SQL Server

In this SQL Server tutorial, you will learn the STRING_SPLIT function in SQL Server to divide the string based on the specified character.

You will understand the benefits of using the STRING_SPLIT() function in SQL Server with its syntax. Then, you will see how the STRING_SPLIT() function works with the help of some examples.

After that, you will understand the ordinal column and how to enable it. Then, you will see a real-world example where you will apply the STRING_SPLIT() function table.

STRING_SPLIT Function in SQL Server

The STRING_SPLIT() function in SQL Server splits the given string into substrings based on the specified delimiter or separator.

STRING_SPLIT() function can be beneficial in the following cases:

  • It can help import and transform data in the ETL (Extract Transform Load) processes.
  •  It helps in converting the normalized data into a normalized format.
  •  It is beneficial for parsing log files or other text data where the values are stored in delimited formats.

The syntax is given below.

STRING_SPLIT(string, separator)

Where,

  • STRING(): The function splits the string into rows of substrings.
  • string: The original string that you want to split. It can be any character datatype such as varchar, nvarchar, char and nchar.
  • separator: It is the character for splitting the string. It can be any single character of type char(1), varchar(1), nvarchar(1), and nchar(1).

The STRING_SPLIT() returns the single-column table by default. The returned single-column table contains the rows that represent the substrings. Also, this function exists in the SQL Server 2016 and the Azure SQL Database.

For example, let’s separate the string ‘SQL,Server,Stored Procedure’. This string contains the comma (, ), so separate the string into substrings based on the comma using the query below.

SELECT VALUE FROM STRING_SPLIT('SQL,Server,Stored Procedure',',');
STRING_SPLIT Function in SQL Server

After executing the above query, the STRING_SPLIT() function returns the single-column table containing the substrings as rows of the given string ‘SQL,Server,Stored Procedure’.

STRING_SPLIT() splits the string into substrings based on the specified separator, which is the comma. It splits the string exactly from the comma.

Let’s separate the string ‘SQL-Server-Instance’ into substrings based on the different separators, such as hyphen (-).

SELECT VALUE FROM STRING_SPLIT('SQL-Server-Instance','-');
STRING_SPLIT Function in SQL Server Based on the Hyphen

As you can see, the string is split into substrings based on the separator, which is a hyphen (-).

This is how you can split the string into substrings based on the specified delimiter in the STRING_SPLIT function in SQL Server.

STRING_SPLIT Function in SQL Server with Ordinal Column

The ordinal column is a special column that shows the order in which the substrings were found in the original string.

So when you split the string using the STRING_SPLIT() function, it returns the table with only one column containing each substring as a separate row. So here, without the ordinal column, you wouldn’t know the order of the original order of these substrings in the input string.

Let’s say you have a string ‘USA, Canada, Australia’, and you split it by the comma. You get a table containing three rows, one for “USA”, one for “Canada”, and one for “Australia”.

But without the ordinal column, you wouldn’t know that “USA” came first, “Canada” second and “Australia” third in the original string.

When an ordinal column exists, it adds numbers like 1, 2, or 3 to each substring, indicating the position of each substring in the original string.

The complete syntax of the STRING_SPLIT() is as follows:

STRING_SPLIT(string, separator, enable_ordinal)

Where,

enable_ordinal: It is the bit or int, where if you specify the 1, enable the ordinal column in the output. Otherwise, for 0, disable the ordinal column.

For example, you have the string ‘USA, Canada, Australia’ and split this string into substrings with the order of each substring.

So, for that, use the below query.

SELECT * FROM STRING_SPLIT('USA, Canada, Australia',',',1);
STRING_SPLIT Function in SQL Server with Ordinal Column

Look in the output of the above query that contains the ordinal column containing the order of each substring such as the order of “USA” is 1, “Canada” is 2 and “Austrailia” is 3.

Let’s understand the query part STRING_SPLIT(‘USA, Canada, Australia’,’,’,1),

  • The first parameter is an original string ‘USA, Canada, Australia’ containing a list of the country names separated by a comma.
  • Then, the second parameter is the separator, which is the comma (, ) in the STRING_SPLIT() function, which means separating the string into substrings based on where the comma appears.
  • After that, the third parameter is the 1 value, which indicates enabling the ordinal column or including the ordinal column in the output containing the order of each substring.

This is how to enable or disable the ordinal column by specifying the one or zero value in the STRING_SPLIT function in SQL Server.

STRING_SPLIT Function in SQL Server with Table

Let’s use the STRING_SPLIT() function with the table. For example, a CustomerOrders table with columns OrderID, CustomerName and OrderDate is shown below.

STRING_SPLIT Function in SQL Server with CustomerOrders Table

Now you have a string with OrderID such as ‘4,5,2,7’, and you must retrieve the customer name and order date. For that, use the below query.

SELECT OrderID, CustomerName, OrderDate
FROM CustomerOrders
JOIN STRING_SPLIT('4,5,2,7',',')
    ON value = OrderID;
STRING_SPLIT Function in SQL Server with Table

As you can see in the above query output, it contains only the four orders with the customer name and order date. For example, order id 2, 4, 5, 7.

But here, you got the four orders with the help of the STRING_SPLIT() function. To understand this, look at the query part JOIN STRING_SPLIT(‘4,5,2,7′,’,’) ON value = OrderID.

  • The STRING_SPLIT() function splits the string ‘4,5,2,7’ into individual elements based on the comma ‘,’ separator. Then, it returns the result, a table-like structure where each value ( 4, 5, 2, 7) is in a separate row as a substring.
  • Then, the JOIN operation is performed between the CustomerOrders table and the table returned by the STRING_SPLIT() function based on the condition ON value = OrderID.
  • Here, ON value = OrderID means join the rows from the CustomerOrders with rows returned from the STRING_SPLIT() function where the OrderID in CustomerOrders matches any of the values (4, 5, 2, 7) generated by STRING_SPLIT() function.

Remember, it doesn’t include the ordinal column if you specify the 0 as the third argument to the STRING_SPLIT() function.

Well, now you clearly understand the workings of the STRING_SPLIT() function in the above query. This is the one way to use the STRING_SPLIT() function, and multiple ways can depend upon the need.

This is how you can use the STRING_SPLIT function in SQL Server with a table column.

Conclusion

In this SQL Server tutorial, you learned how to split the string into substrings using the STRING_SPLIT() function in SQL Server. Then, you learned how to include the order of each substring in the result set by enabling the ordinal column. Finally, the STRING_SPLIT() function on the table retrieves the customer name and order date based on the order ID.

You may like to read: