Convert Delimited Data Into Columns In SQL Server

This article addresses the conversion of the delimited data into columns in SQL Server. There are two functions called “STRING_SPLIT” and “PARSENAME” that helps us convert the delimited data into a single and multiple columns.

Consider the below data set for practice.

Data
1456, ConvertedData1, SomeOtherData1
1466, ConvertedData2, SomeOtherData2
1476, ConvertedData3, SomeOtherData4

Let’s create the table and load data in it.

— Create table statement
CREATE TABLE splitStringData(dataCol1 VARCHAR(MAX))

— Data Insertion
INSERT INTO splitStringData VALUES
(‘1456, ConvertedData1, SomeOtherData1’),
(‘1466, ConvertedData2, SomeOtherData2’),
(‘1476, ConvertedData3, SomeOtherData4’)

PARSENAME – Description:
Returns the specified part of an object name. The parts of an object that can be retrieved are the object name, schema name, database name, and server name. The PARSENAME function does not indicate whether an object by the specified name exists. PARSENAME just returns the specified part of the specified object name.

Syntax:
PARSENAME (‘object_name’ , object_piece )

STRING_SPLIT – Description:
A table-valued function that splits a string into rows of substrings, based on a specified separator character. STRING_SPLIT requires the compatibility level to be at least 130. When the level is less than 130, SQL Server is unable to find the STRING_SPLIT function.

Syntax:
STRING_SPLIT ( string , separator )

Method-1 : Splitting delimited string values into multiple columns
SELECT
PARSENAME(REPLACE(dataCol1, ‘,’, ‘.’), 1) AS [SomeID]
, PARSENAME(REPLACE(dataCol1, ‘,’, ‘.’), 2) AS [Col1]
, PARSENAME(REPLACE(dataCol1, ‘,’, ‘.’), 3) AS [Col2]
FROM dbo.splitStringData;

The above returns the result set in reverse order.
SomeID Col1 Col2
SomeOtherData1 ConvertedData1 1456
SomeOtherData2 ConvertedData2 1466
SomeOtherData4 ConvertedData3 1476

So, if we want them in order, use ‘REVERSE’ function as shown below.

SELECT
REVERSE(PARSENAME(REPLACE(REVERSE(dataCol1), ‘,’, ‘.’), 1)) AS [SomeID]
, REVERSE(PARSENAME(REPLACE(REVERSE(dataCol1), ‘,’, ‘.’), 2)) AS [Col1]
, REVERSE(PARSENAME(REPLACE(REVERSE(dataCol1), ‘,’, ‘.’), 3)) AS [Col2]
FROM dbo.splitStringData;

The result is as shown below:
SomeID Col1 Col2
1456 ConvertedData1 SomeOtherData1
1466 ConvertedData2 SomeOtherData2
1476 ConvertedData3 SomeOtherData4

SQL Server REVERSE PARSE2

Method-2 : Splitting delimited string values into single column
If you want to split the data in a single column, use STRING_SPLIT function.

SELECT value FROM splitStringData
CROSS APPLY STRING_SPLIT(dataCol1,’,’)

SQL Server STRING_SPLIT