0

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?

0

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.

|improve this answer|||||
3

From comments:

  1. 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

  2. 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

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

|improve this answer|||||
0
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

It was pretty much this - it sucked!

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.