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