How did you split strings before string_split() 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?
From comments:



  The built-in STRING_SPLIT() function was added in SQL Server 2016, not 2014. And for me, I go the SQLCLR route because I am not using Azure Single DB or AWS RDS and thus have no reason not to. I use the SQLCLR library that I created, SQL#, which contains String_Split and String_SplitInts (optimized for splitting a list of BIGINT / INT / SMALLINT / TINYINT values). The "4k" versions of both are to be used when the input string is guaranteed to never be more than NVARCHAR(4000) as they are faster than the non-"4k" versions. – Solomon Rutzky 


  there were lots of User-Defined Table-Valued functions floating around that did this. Here's a survey article by Aaron Bertrand: sqlperformance.com/2012/07/t-sql-queries/split-strings – David Browne - Microsoft


  this is probably the most efficient by Jeff Moden sqlservercentral.com/articles/… – Squirrel