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))

INSERT InvoiceString VALUES ('123|2016-05-25|8000')
INSERT InvoiceString VALUES ('456|2017-05-25|4000')
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:

		SELECT CAST('<T><C>' + REPLACE(Col1,'|','</C><C>') + '</C></T>' AS XML) AS X
		FROM InvoiceString
	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

The strings are splitted into three columns:

Split String To Columns Results From Demo
Split String To Columns Results From Demo

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

Leave a Reply