String_split

[Galaxiom]

We upgraded our SQL server recently and today I found a feature we didn't have before that is making my life very easy.

STRING_SPLIT is does what it says with a nominated deliminator just like VBA Split() except it returns a table with a separate record for each item.Microsoft introduced it in Server 2016.

I was able to write a straightforward query that selected a record, returned a file stored as binary, converted it to text, split it into lines, selected the data lines from the page headers and footer, parsed the lines using a join to a table of parameters for SubString() then Pivot the results into columns.

It nearly instantly creates a recordset from the text. I previously did this task with a pile of VBA code in Access reading from files on the file system. That process took a whole lot longer.

 

[WayneRyan]

Galixiom,

Jeff Moden from sqlservercentral.com developed a great splitter:

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-“csv-splitter”-function

I've been using it for quite a while and its great.

It very fast and easy to use. It also PRESERVES THE ORDER of the input string.

That might be an important consideration ... sometimes order matters.

A VBA splitter sounds good, but it'll never beat either of the above for speed.

Wayne

 

[Galaxiom]

Galixiom,

Jeff Moden from sqlservercentral.com developed a great splitter:

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-“csv-splitter”-function

Perfect. I have recently been trying to find that series of articles. I read them a few years ago.

I have been able to use String_Split on some tasks where the data has numbered lines but have some others where the order is a problem.

 

[NauticalGent]

You guys may have provided me with an answer to my text parsing problem. Best of all, this will be done on the server side making life a WHOLE lot better.

I can't test this until I get back to work but I thank both of you in advance...