String_split (1 Viewer)

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 07:05
Joined
Jan 20, 2009
Messages
12,849
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

AWF VIP
Local time
Today, 20:05
Joined
Nov 19, 2002
Messages
7,122

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 07:05
Joined
Jan 20, 2009
Messages
12,849

NauticalGent

Ignore List Poster Boy
Local time
Today, 16:05
Joined
Apr 27, 2015
Messages
6,286
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...
 

Users who are viewing this thread

Top Bottom