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/C','INT') AS InvoiceNo ,X.value('/T/C','DATE') AS InvoiceDate ,X.value('/T/C','INT') AS Amount FROM CTE_Split
The strings are splitted into three columns: