Split delimited text into columns in SQL Server

This tutorial is about how to split a delimited text into multiple independent columns with SQL Server. Working with comma or semi-colon delimited data often requires efficient and effective manipulation techniques. A common task is to split a single text variable into multiple columns.

This is particularly useful for preparing CSV file imports or restructuring data. In this article, we’ll explore a straightforward method to achieve this using T-SQL. Indeed, in the previous tutorial about splitting strings, we discussed how to split a delimited text stored in a variable into one single column, one value per column.

Table of Contents

SQL Server script to split delimited text into multiple columns

So this script allows you to split a string composed of semi-columns separated data into multiple hard coded columns.

-- Declare the variables
DECLARE
   @String nvarchar(max),
   @Delimiter char(1);

-- Initialize the string and the delimiter
SELECT @String = 'New York;Los Angeles;Chicago;Houston;Phoenix;Philadelphia;San Antonio;San Diego;Dallas;San Jose',
       @Delimiter = ';'
;

-- Split the string into multiple columns
WITH cte AS (
    SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as id
    FROM STRING_SPLIT(@String, @Delimiter)
)

SELECT
    MAX(CASE WHEN id = 1 THEN value END) AS column_1,
    MAX(CASE WHEN id = 2 THEN value END) AS column_2,
    MAX(CASE WHEN id = 3 THEN value END) AS column_3,
    MAX(CASE WHEN id = 4 THEN value END) AS column_4,
    MAX(CASE WHEN id = 5 THEN value END) AS column_5,
    MAX(CASE WHEN id = 6 THEN value END) AS column_6,
    MAX(CASE WHEN id = 7 THEN value END) AS column_7,
    MAX(CASE WHEN id = 8 THEN value END) AS column_8,
    MAX(CASE WHEN id = 9 THEN value END) AS column_9,
    MAX(CASE WHEN id = 10 THEN value END) AS column_10
FROM cte;

The provided query demonstrates how to split a semicolon-separated string into individual columns. The steps are as follows:

Explanation of the SQL Server code to split a delimited string

This section explains step by step the code above to better understand the concepts. The code simply split the semicolon delimited text into distinct columns using SQL Server code.

Step 1: Declare the variables

We start by declaring two variables:

@String for the text to be split
@Delimiter for the separator character

DECLARE
   @String nvarchar(max),
   @Delimiter char(1);

Step 2: Initialize the variables

Initialize @String with the values separated by semicolons and set @Delimiter to the semicolon character.

SELECT @String = 'New York;Los Angeles;Chicago;Houston;Phoenix;Philadelphia;San Antonio;San Diego;Dallas;San Jose', @Delimiter = ';'

Step 3: Split the string

The STRING_SPLIT function is used within a Common Table Expression (CTE) to split the string. ROW_NUMBER assigns a unique sequential integer to each split value.

WITH cte AS (
    SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as id
    FROM STRING_SPLIT(@String, @Delimiter)
)

Step 4: Select the columns

Finally, we use a SELECT statement with multiple MAX(CASE...) clauses to pivot these values into separate columns.

SELECT
    MAX(CASE WHEN id = 1 THEN value END) AS column_1,
    MAX(CASE WHEN id = 2 THEN value END) AS column_2,
    MAX(CASE WHEN id = 3 THEN value END) AS column_3,
    MAX(CASE WHEN id = 4 THEN value END) AS column_4,
    MAX(CASE WHEN id = 5 THEN value END) AS column_5,
    MAX(CASE WHEN id = 6 THEN value END) AS column_6,
    MAX(CASE WHEN id = 7 THEN value END) AS column_7,
    MAX(CASE WHEN id = 8 THEN value END) AS column_8,
    MAX(CASE WHEN id = 9 THEN value END) AS column_9,
    MAX(CASE WHEN id = 10 THEN value END) AS column_10
FROM cte;

As we could see, the code is relatively static and adaptable, but achieves a different purpose than the STRING_SPLIT built in function.

A convenient pure T-SQL solution to split a string into columns

This query is a simple yet effective way to split a delimited text into multiples columns in SQL Server. It’s suitable for scenarios where the number of columns is predetermined and does not exceed ten. Of course it is very easily extendable. But it is a static solution.

For more complex cases, such as a variable number of columns, dynamic SQL can be employed. This method is a handy tool for database developers looking to streamline their data import and manipulation processes in SQL Server.

https://expert-only.com/en/t-sql/split-string-with-delimiter-in-sql-server/