SQL Server Split String into Rows

In this SQL Server tutorial, You will learn about SQL Server split string into rows.

As a database developer or data engineer, sometimes, you need to split a string into rows while processing data.

You will understand two methods to split a string into rows; in the first method, you will use the STRING_SPLIT() function, and in the second, you will use the common table expression.

SQL Server Split String into Rows

Splitting a string into rows is a data manipulation task in the database. Suppose you have a string containing portions of information separated by a delimiter and need to split or separate this information into rows.

Let me explain in detail what SQL Server split string into rows means. Here, you have to split the string information into individual rows.

For example, you have a sequence of characters ( or string) as ‘sql server database’; here, the word of this string is separated by space (which is delimiter). After splitting this string into rows, it would look like the following:

sql
server
database

As you can see, the string is split or separated into three rows based on the delimiter (space).

Similarly, you must split a string in SQL Server using any function or method. There are several methods for splitting a string into rows.

SQL Server Split String into Rows using STRING_SPLIT()

The SQL server has a method or function called STRING_SPLIT(), which splits the string based on the specified delimiter, such as space, comma, semicolon or any custom characters.

To know more about STRING_SPLIT() in more detail, refer to this tutorial on the STRING_SPLIT Function in SQL Server.

However, the syntax for using this function is given below.

STRING_SPLIT(string, separator)

Where,

  • string: It is the string that you want to split.
  • separator: It is a separator based on a string divided into pieces of information.

For example, suppose you have the string ‘I work in the USA’; this string contains different worlds separated by space. So, split the string into rows using the STRING_SPLIT() function.

SELECT VALUE FROM STRING_SPLIT('I work in USA',' ');
SQL Server Split String into Rows using STRING_SPLIT()

In the output, the STRING_SPLIT() function separated the string into 3 rows containing the values ‘I’, ‘work’, ‘in’, and ‘USA’.

Instead of a string value, you can also pass the column to the STRING_SPLIT() function to split the column values into rows.

This is how you can use the STRING_SPLIT() to split a string into rows in SQL Server.

SQL Server Split String into Rows using Common Table Expression (CTE)

CTE is used for temporary results; this temp result can be used in the SELECT, INSERT, UPDATE and DELETE statements.

Here, I will show you how to use the CTE to split the string into rows; for example, you have the exact string ‘I, work, in, USA’, but here, string information is separated by a comma. To split this string using CTE, look at the below query or execute it.

First, create a table and insert the data using the query below.

CREATE TABLE StringTable (
	id INT IDENTITY(1,1),
	strValue VARCHAR(MAX)
);

INSERT INTO StringTable (strValue) VALUES ('I, work, in, USA');

The StringTable contain a single string value.

Next, use the CTE to split the string value in a table into rows, as shown below.

WITH cte_split_string(id, split_values, strValue) AS (
	-- anchor member
	SELECT
    	id,
    	LEFT(strValue, CHARINDEX(',', strValue + ',') - 1),
    	STUFF(strValue, 1, CHARINDEX(',', strValue + ','), '')
	FROM StringTable

	UNION ALL

	-- recursive member
	SELECT
    	id,
    	LEFT(strValue, CHARINDEX(',', strValue + ',') - 1),
    	STUFF(strValue, 1, CHARINDEX(',', strValue + ','), '')
	FROM cte_split_string
	WHERE strValue > ''
)

SELECT id, LTRIM(RTRIM(split_values)) AS split_values
FROM cte_split_string
ORDER BY id;
SQL Server Split String into Rows using Common Table Expression (CTE)

Look at the above output; the string ‘I, work, in, USA’ is divided into separate rows using the common table expression (CTE).

This is how SQL Server split string into rows using common table expression (CTE).

Conclusion

This SQL Server tutorial taught you about SQL Server Split String into Rows.

You have used the STRING_SPLIT() function and common table expression to split a string into rows.

You may like to read: