Avithohol Avithohol - 1 year ago 53
SQL Question

Mimic STRING_SPLIT without custom function in SQL 2008

It is asked many times, but not this way.

I am on SQL 2008, and there is no STRING_SPLIT function (like in 2016).

A query returns with the following row, see below a single example row. What you see below in bald is a single field actually, so one varchar column has it altogether:

which I'd like to be split by pipe | character.

I cannot write a CTE for this, or a custom function.
I have to extract the individual pipe delimited elements, into different columns, within one select statement using the built in string functions like CHARINDEX,PATINDEX.

Anybody has any idea?

Answer Source
DECLARE @Result Table(Value varchar(50))
SELECT @X = CAST('<A>' + REPLACE(@StringList, '|', '</A><A>') + '</A>' AS XML)

SELECT t.value('.', 'varchar(50)') as inVal
FROM @X.nodes('/A') AS x(t)

This will create a table with one column (Value). Each split value from your pipe-delimited string will create a new record in this table. Then you can join to it however you'd like. Please let me know if this is unclear or if it doesn't work on SQL 2008.

You can increase the size of the varchar, if needed - and you can modify the query to split on different values (comma-delimited, etc.).