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

SQL Server REVERSE PARSE

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

Similar implementation in other RDBMS platforms –

7 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s