Sql-server – How did you split strings before string_split()

sql serversql server 2014string-splittingt-sql

I have some strings I need to split on a SQL Server 2014 instance.

I would normally do it something like this:

declare @bk nvarchar(1000)

set @bk = 'SIS5.0~person_id~696969'

select value from string_split(@bk, '~')

and I can also do it like this:

declare @bk nvarchar(1000)

set @bk = 'SIS5.0~person_id~696969'

;with hizizzle as (
select left(@bk, charindex('~', @bk)-1) as flippity, right(@bk, len(@bk) - charindex('~', @bk)) as floppity)

select flippity, left(floppity, charindex('~',floppity)-1) as floppity, 
right(floppity, len(floppity) - charindex('~', floppity)) as flooo 
from hizizzle

…but there must be a better way, right?

Question: How did you do string splits in the ancient past before 2014?

Best Answer

If you know the number of items and want to access one or more by its index, you can do something like the following:

DECLARE @bk nvarchar(1000)

SET @bk = 'SIS5.0~person_id~696969'

SELECT
CAST(N'<x>' + REPLACE(@bk, '~', N'</x><x>') + N'</x>' AS XML).value('/x[1]', 'nvarchar(max)') [string1],
CAST(N'<x>' + REPLACE(@bk, '~', N'</x><x>') + N'</x>' AS XML).value('/x[2]', 'nvarchar(max)') [string2],
CAST(N'<x>' + REPLACE(@bk, '~', N'</x><x>') + N'</x>' AS XML).value('/x[3]', 'nvarchar(max)') [string3]


string1    string2    string3
---------- ---------- -------
SIS5.0     person_id  696969

Splits on the '~' character, surrounds with simple tags, then uses the .value property to get a specific index.

Works for 2008, and I believe (but can't remember) it also worked on 2005.

Related Question