Split strings to columns

A short demo on how to split strings with delimiters into columns.

In this demonstration I have delimited strings of invoices that I want to split into separate columns. The column separator character is |, but it could be something else. To create the sample data, run the following T-SQL (in tempdb for instance):

CREATE TABLE InvoiceString (Col1 VARCHAR(1000))
GO

INSERT InvoiceString VALUES ('123|2016-05-25|8000')
INSERT InvoiceString VALUES ('456|2017-05-25|4000')
GO
Split String To Columns Demo

Split String To Columns Demo

The columns are InvoiceNo, InvoiceDate and Amount.

To split the strings into separate columns, run the following T-SQL:

;WITH CTE_Split AS
	(
		SELECT CAST('<T><C>' + REPLACE(Col1,'|','</C><C>') + '</C></T>' AS XML) AS X
		FROM InvoiceString
	)
SELECT
	X.value('/T[1]/C[1]','INT') AS InvoiceNo
	,X.value('/T[1]/C[2]','DATE') AS InvoiceDate
	,X.value('/T[1]/C[3]','INT') AS Amount
FROM CTE_Split

The strings are splitted into three columns:

Split String To Columns Results From Demo

Split String To Columns Results From Demo

Leave a Reply

Required fields are marked *.