Split string into multiple entries based upon known static length

Question

Split string into multiple entries based upon known static length

mciesiensky

SSC Enthusiast

Points: 195
More actions
March 8, 2016 at 2:45 pm

#311971

I have several tables of data where one field holds multiple records of "n" static length.
In the example provided below, the length is always 8.
Also, some of the field lengths greatly exceed 800 characters, hence the TEXT data type.
I have utilized the DelimitedSplit8K function in several other instances where there were actual delimiters, but this goes even beyond that.
(I did this once before eons ago using Visual Basic and wrote an external program to parse the data into individual lines for processing. Just can't find the source. Was probably RBAR anyway.)
The last line of data is a sample where the data exceed 8k characters.
I can break up the entry at 8K segments for processing, I just don't have a quick and simple way to break up the rest without going RBAR.
Any help is greatly appreciated.
Sample output based upon the first data record below would be
[font="Courier New"] 00002106
00002107
00002108
00002109
00002110
[/font]
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
ValueList TEXT,
ValueCount INT
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, ValueList, ValueCount)
SELECT '15','0000210600002107000021080000210900002110','5' UNION ALL
SELECT '17','00002132000021330000213400002135','4' UNION ALL
SELECT '18','000021360000213700002138000021390000214000002141','6' UNION ALL
SELECT '19','0000214200002143000021440000214500002146','5' UNION ALL
SELECT '20','00002147000021480000214900002150','4' UNION ALL
SELECT '21','00002151000021520000215300002154','4' UNION ALL
SELECT '23','000021700000217100002172','3' UNION ALL
SELECT '25','0000218300002184000021850000218600002187','5' UNION ALL
SELECT '26','00002188000021890000219000002191','4' UNION ALL
SELECT '28','0000220500002206000022070000220800002209000022100000221100002212','8' UNION ALL
SELECT '1009','0006433900064340000643410006434200064343000643440006434500064346000643470006435100064352000643530006435400064355000643560006435700064358000643590006436000064361000643620006436300064364000643650006436600064367000643680006436900064370000643710006437200064373000643740006437500064376000643770006437800064379000643800006438100064382000643830006438400064385000643860006438700064388000643890006439000064391000643920006439300064394000643950006439600064397000643980006439900064400000644010006440200064403000644040006440500064406000644070006440800064409000644100006441100064412000644130006441400064415000644160006441700064418000644190006442000064421000644220006442300064424000644250006442600064427000644280006442900064430000644310006443200064433000644340006443500064436000644370006443800064439000644400006444100064442000644430006444400064445000644460006444700064448000644490006445000064451000644520006445300064454000644550006445600064457000644580006445900064460000644610006446200064463000644640006446500064466000644670006446800064469000644700006447100064472000644730006447400064475000644760006447700064478000644790006448000064481000644820006448300064484000644850006448600064487000644880006448900064490000644910006449200064493000644940006449500064496000644970006449800064499000645000006450100064502000645030006450400064505000645060006450700064508000645090006451000064511000645120006451300064514000645150006451600064517000645180006451900064520000645210006452200064523000645240006452500064526000645270006452800064529000645300006453100064532000645330006453400064535000645360006453700064538000645390006454000064541000645420006454300064544000645450006454600064547000645480006454900064550000645510006455200064553000645540006455500064556000645570006455800064559000645600006456100064562000645630006456400064565000645660006456700064568000645690006457000064571000645720006457300064574000645750006457600064577000645780006457900064580000645810006458200064583000645840006458500064586000645870006458800064589000645900006459100064592000645930006459400064595000645960006459700064598000645990006460000064601000646020006460300064604000646050006460600064607000646080006460900064610000646110006461200064613000646140006461500064616000646170006461800064619000646200006462100064622000646230006462400064625000646260006462700064628000646290006463000064631000646320006463300064634000646350006463600064637000646380006463900064640000646410006464200064643000646440006464500064646000646470006464800064649000646500006465100064652000646530006465400064655000646560006465700064658000646590006466000064661000646620006466300064664000646650006466600064667000646680006466900064670000646710006467200064673000646740006467500064676000646770006467800064679000646800006468100064682000646830006468400064685000646860006468700064688000646890006469000064691000646920006469300064694000646950006469600064697000646980006469900064700000647010006470200064703000647040006470500064706000647070006470800064709000647100006471100064712000647130006471400064715000647160006471700064718000647190006472000064721000647220006472300064724000647250006472600064727000647280006472900064730000647310006473200064733000647340006473500064736000647370006473800064739000647400006474100064742000647430006474400064745000647460006474700064748000647490006475000064751000647520006475300064754000647550006475600064757000647580006475900064760000647610006476200064763000647640006476500064766000647670006476800064769000647700006477100064772000647730006477400064775000647760006477700064778000647790006478000064781000647820006478300064784000647850006478600064787000647880006478900064790000647910006479200064793000647940006479500064796000647970006479800064799000648000006480100064802000648030006480400064805000648060006480700064808000648090006481000064811000648120006481300064814000648150006481600064817000648180006481900064820000648210006482200064823000648240006482500064826000648270006482800064829000648300006483100064832000648330006483400064835000648360006483700064838000648390006484000064841000648420006484300064844000648450006484600064847000648480006484900064850000648510006485200064853000648540006485500064856000648570006485800064859000648600006486100064862000648630006486400064865000648660006486700064868000648690006487000064871000648720006487300064874000648750006487600064877000648780006487900064880000648810006488200064883000648840006488500064886000648870006488800064889000648900006489100064892000648930006489400064895000648960006489700064898000648990006490000064901000649020006490300064904000649050006490600064907000649080006490900064910000649110006491200064913000649140006491500064916000649170006491800064919000649200006492100064922000649230006492400064925000649260006492700064928000649290006493000064931000649320006493300064934000649350006493600064937000649380006493900064940000649410006494200064943000649440006494500064946000649470006494800064949000649500006495100064952000649530006495400064955000649560006495700064958000649590006496000064961000649620006496300064964000649650006496600064967000649680006496900064970000649710006497200064973000649740006497500064976000649770006497800064979000649800006498100064982000649830006498400064985000649860006498700064988000649890006499000064991000649920006499300064994000649950006499600064997000649980006499900065000000650010006500200065003000650040006500500065006000650070006500800065009000650100006501100065012000650130006501400065015000650160006501700065018000650190006502000065021000650220006502300065024000650250006502600065027000650280006502900065030000650310006503200065033000650340006503500065036000650370006503800065039000650400006504100065042000650430006504400065045000650460006504700065048000650490006505000065051000650520006505300065054000650550006505600065057000650580006505900065060000650610006506200065063000650640006506500065066000650670006506800065069000650700006507100065072000650730006507400065075000650760006507700065078000650790006508000065081000650820006508300065084000650850006508600065087000650880006508900065090000650910006509200065093000650940006509500065096000650970006509800065099000651000006510100065102000651030006510400065105000651060006510700065108000651090006511000065111000651120006511300065114000651150006511600065117000651180006511900065120000651210006512200065123000651240006512500065126000651270006512800065129000651300006513100065132000651330006513400065135000651360006513700065138000651390006514000065141000651420006514300065144000651450006514600065147000651480006514900065150000651510006515200065153000651540006515500065156000651570006515800065159000651600006516100065162000651630006516400065165000651660006516700065168000651690006517000065171000651720006517300065174000651750006517600065177000651780006517900065180000651810006518200065183000651840006518500065186000651870006518800065189000651900006519100065192000651930006519400065195000651960006519700065198000651990006520000065201000652020006520300065204000652050006520600065207000652080006520900065210000652110006521200065213000652140006521500065216000652170006521800065219000652200006522100065222000652230006522400065225000652260006522700065228000652290006523000065231000652320006523300065234000652350006523600065237000652380006523900065240000652410006524200065243000652440006524500065246000652470006524800065249000652500006525100065252000652530006525400065255000652560006525700065258000652590006526000065261000652620006526300065264000652650006526600065267000652680006526900065270000652710006527200065273000652740006527500065276000652770006527800065279000652800006528100065282000652830006528400065285000652860006528700065288000652890006529000065291000652920006529300065294000652950006529600065297000652980006529900065300000653010006530200065303000653040006530500065306000653070006530800065309000653100006531100065312000653130006531400065315000653160006531700065318000653190006532000065321000653220006532300065324000653250006532600065327000653280006532900065330000653310006533200065333000653340006533500065336000653370006533800065339000653400006534100065342000653430006534400065345000653460006534700065348000653490006535000065351000653520006535300065354000653550006535600065357000653580006535900065360000653610006536200065363000653640006536500065366000653670006536800065369000653700006537100065372000653730006537400065375000653760006537700065378000653790006538000065381000653820006538300065384000653850006538600065387000653880006538900065390', '1049'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Using a tally table and SUBSTRING(), it's very easy to accomplish this. In this example, I'm using a cte to create the tally table on the fly.

Consider changing the text data type into a varchar(max).

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

E2(n) AS(

SELECT a.n FROM E a, E b

),

E4(n) AS(

SELECT a.n FROM E2 a, E2 b

),

cteTally(n) AS(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E4

)

SELECT m.*, SUBSTRING( m.ValueList, (n*8)-7, 8)

FROM #mytable m

JOIN cteTally t ON m.ValueCount >= t.n

ORDER BY ID, n;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mciesiensky SSC Enthusiast Points: 195 More actions · Answer 2

And how would I add a counter column to keep the results ordered? Simple count starting at 1 would suffice. (I should have added that in my original post.)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

The code already has that value. If you can't find it, maybe you're not understanding how this works.

Please, try to explain it and ask any questions you might have.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mciesiensky SSC Enthusiast Points: 195 More actions · Answer 4

Sorry. Took me a minute to see the "t.n" in the code. Added that to my output and I think that will work.

Now to convert that to a function similar to DelimitedSplit8K.

:-):-)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

mciesiensky (3/8/2016)
Sorry. Took me a minute to see the "t.n" in the code. Added that to my output and I think that will work.
Now to convert that to a function similar to DelimitedSplit8K.
:-):-)

I actually have a function that, in the end, uses very similar tally table logic to Luis' solution. There's actually two functions involved but they're they can both be used for many other things than the problem you you're solving today. Note the comments section of each for details about the function. First, NGrams2B:

CREATE FUNCTION dbo.NGrams2B

(

@string varchar(max),

@N int

)

/****************************************************************************************

Purpose:

A character-level @N-Grams function that outputs a stream of tokens based on an input

string (@string) up to 2^31-1 bytes (2 GB). For more

information about N-Grams see: http://en.wikipedia.org/wiki/N-gram.

Compatibility:

SQL Server 2008+ and Azure SQL Database

Syntax:

--===== Autonomous

SELECT position, token FROM dbo.NGrams2B(@string,@N);

--===== Against a table using APPLY

SELECT s.SomeID, ng.position, ng.string

FROM dbo.SomeTable s

CROSS APPLY dbo.NGrams2B(s.SomeValue,@N) ng;

Parameters:

@string = The input string to split into tokens.

@N = The size of each token returned.

Returns:

Position = bigint; the position of the token in the input string

token = varchar(max); a @N-sized character-level N-Gram token

Examples:

--===== Turn the string, 'abcd' into unigrams, bigrams and trigrams

SELECT position, token FROM dbo.NGrams2B('abcd',1); -- bigrams (@N=1)

SELECT position, token FROM dbo.NGrams2B('abcd',2); -- bigrams (@N=2)

SELECT position, token FROM dbo.NGrams2B('abcd',3); -- trigrams (@N=3)

--===== To avoid an implicit converstion

SELECT position, token FROM dbo.NGrams2B(CONVERT(varchar(max),'abcd',0),CAST(1 AS bigint));

Parameters:

@string = varchar(max); the input string to split into tokens

@N = bigint; the size of each token returned

Developer Notes:

1. Based on NGrams8k but modified to accept varchar(max)

2. Performs about 2-3 times slower than NGrams8k. Only use when you are sure

that NGrams8k will not suffice.

3. Many functions that use NGrams2B will see a huge performance gain when the optimizer

creates a parallel query plan. One way to get a parallel query plan (if the optimizer

does not chose one) is to use make_parallel by Adam Machanic which can be found here:

sqlblog.com/blogs/adam_machanic/archive/2013/07/11/next-level-parallel-plan-porcing.aspx

4. When @N is less than 1 or greater than the datalength of the input string then no

tokens (rows) are returned.

5. This function can also be used as a tally table with the position column being your

"N" row. To do so use REPLICATE to create an imaginary string, then use NGrams8k to

split it into unigrams then only return the position column. NGrams2B will get you up

to 2,176,782,336 numbers. There will be no performance penalty for sorting by position

in ascending order but there is for sorting in descending order. Example:

Pseudo Tally Table Examples:

--===== (1) Get the numbers 1 to 100000 in ascending order:

SELECT N = position FROM dbo.NGrams2B(REPLICATE(CAST(0 AS varchar(max)),100000),1);

--===== (2) Get the numbers 1 to 100000 in descending order:

DECLARE @maxN bigint = 100000;

SELECT N = @maxN-position+1

FROM dbo.NGrams2B(REPLICATE(CAST(0 AS varchar(max)),@maxN),1)

ORDER BY position;

-- note that you don't need a variable, I used one to make this easier to understand.

---------------------------------------------------------------------------------------

Revision History:

Rev 00 - 09/09/2015 Initial Developement - Alan Burstein

Rev 01 - 10/29/2015 Added ISNULL logic to the TOP clause for both parameters: @string

and @N. This will prevent a NULL string or NULL @N from causing an

"improper value" to be passed to the TOP clause. - Alan Burstein

****************************************************************************************/

RETURNS TABLE WITH SCHEMABINDING AS RETURN

WITH L1(N) AS

(

SELECT N

FROM (VALUES

(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),

(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(N)

), --216 values

iTally(N) AS

(

SELECT

TOP (

ABS(CONVERT(BIGINT,

(DATALENGTH(ISNULL(CAST(@string AS varchar(max)),'')) - (ISNULL(@N,1)-1)),0))

)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM L1 a CROSS JOIN L1 b CROSS JOIN L1 c CROSS JOIN L1 d

--2,176,782,336 rows: enough to handle varchar(max) -> 2^31-1 bytes

)

SELECT

position = N,

token = SUBSTRING(@string,N,@N)

FROM iTally -- @N must be BETWEEN 1 and the length of the string

WHERE @N > 0 AND @N <= DATALENGTH(CAST(@string AS varchar(max)));

The function is used to create a fixed-length splitter I call NSplit2B:

CREATE FUNCTION dbo.NSplit2B(@string varchar(max), @length int, @delimiterLength int)

/****************************************************************************************

Purpose:

Splits a string into @length-sized tokens with @delimiterLength-sized gaps between each

token. Intended to be a better performing alternative to the traditional "splitter" for

cases where the size of the item/token is always the same.

Compatibility:

SQL Server 2008+ and Azure SQL Database

Syntax:

SELECT ItemNumber, Token

FROM dbo.NSplit2B(@string, @length, @delimiterLength)

Parameters:

@string = varchar(max); the input string to "split" into tokens

@length = int; the size of the output token

@delimiterLength = int; the size of the delimiter. Can be set to 0 for when there's

no delimiter

Returns:

ItemNumber = bigint; the represents the order that the token appears in the string

Token = varchar(8000); the @lengh-sized token returned by "splitter."

Developer notes:

1. If the final string is not as long as @length then that row will be truncated.

2. There are no safeguards built into the function to make sure the data is formatted as

expected. Know your data.

Examples:

--===== extract the values "ab", "cd", "ef", and "gg"; token-size = 2; delimiter = "," (1)

SELECT * FROM dbo.NSplit2B('ab,cd,ef,gg',2,1);

--===== extract the values "xxx", "yyy", "bbb", etc... token-size = 3; delimiter = "," (1)

DECLARE @string1 varchar(100) = 'xxx,yyy,bbb,123,zzz';

SELECT * FROM dbo.NSplit2B(@string1,3,1);

--===== extract the values "<client01>", "<client02>", etc...

DECLARE @string varchar(100) = '<client01>,<client02>,<client03>,<client04>';

SELECT * FROM dbo.NSplit2B(SUBSTRING(@string,2,LEN(@string)-2),8,3);

--===== If you dont need a delimiter to split the string

SELECT * FROM dbo.NSplit2B('abcdefghi',3,0);

--===== You can go with negative for @delimiterLength for some overlap

SELECT * FROM dbo.NSplit2B('abcdefghi',3,-1);

----------------------------------------------------------------------------------------

Rev 01 - 20160308 Initial Development - Alan Burstein

****************************************************************************************/

RETURNS TABLE WITH SCHEMABINDING AS RETURN

SELECT

ItemNumber = ROW_NUMBER() OVER (ORDER BY position),

token

FROM dbo.NGrams2B(@string,@length)

WHERE @length+@delimiterLength<>0

AND 1 = position%(@length+@delimiterLength);

The cool thing here is that you can lose the "valuecount". Again, the logic is similar but this code is very re-usable. It also does the job on your sample data with less reads...

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table

ValueList TEXT,

ValueCount INT

)

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table

INSERT INTO #mytable

(ID, ValueList, ValueCount)

SELECT '15','0000210600002107000021080000210900002110','5' UNION ALL

SELECT '17','00002132000021330000213400002135','4' UNION ALL

SELECT '18','000021360000213700002138000021390000214000002141','6' UNION ALL

SELECT '19','0000214200002143000021440000214500002146','5' UNION ALL

SELECT '20','00002147000021480000214900002150','4' UNION ALL

SELECT '21','00002151000021520000215300002154','4' UNION ALL

SELECT '23','000021700000217100002172','3' UNION ALL

SELECT '25','0000218300002184000021850000218600002187','5' UNION ALL

SELECT '26','00002188000021890000219000002191','4' UNION ALL

SELECT '28','0000220500002206000022070000220800002209000022100000221100002212','8' UNION ALL

SELECT '1009','0006433900064340000643410006434200064343000643440006434500064346000643470006435100064352000643530006435400064355000643560006435700064358000643590006436000064361000643620006436300064364000643650006436600064367000643680006436900064370000643710006437200064373000643740006437500064376000643770006437800064379000643800006438100064382000643830006438400064385000643860006438700064388000643890006439000064391000643920006439300064394000643950006439600064397000643980006439900064400000644010006440200064403000644040006440500064406000644070006440800064409000644100006441100064412000644130006441400064415000644160006441700064418000644190006442000064421000644220006442300064424000644250006442600064427000644280006442900064430000644310006443200064433000644340006443500064436000644370006443800064439000644400006444100064442000644430006444400064445000644460006444700064448000644490006445000064451000644520006445300064454000644550006445600064457000644580006445900064460000644610006446200064463000644640006446500064466000644670006446800064469000644700006447100064472000644730006447400064475000644760006447700064478000644790006448000064481000644820006448300064484000644850006448600064487000644880006448900064490000644910006449200064493000644940006449500064496000644970006449800064499000645000006450100064502000645030006450400064505000645060006450700064508000645090006451000064511000645120006451300064514000645150006451600064517000645180006451900064520000645210006452200064523000645240006452500064526000645270006452800064529000645300006453100064532000645330006453400064535000645360006453700064538000645390006454000064541000645420006454300064544000645450006454600064547000645480006454900064550000645510006455200064553000645540006455500064556000645570006455800064559000645600006456100064562000645630006456400064565000645660006456700064568000645690006457000064571000645720006457300064574000645750006457600064577000645780006457900064580000645810006458200064583000645840006458500064586000645870006458800064589000645900006459100064592000645930006459400064595000645960006459700064598000645990006460000064601000646020006460300064604000646050006460600064607000646080006460900064610000646110006461200064613000646140006461500064616000646170006461800064619000646200006462100064622000646230006462400064625000646260006462700064628000646290006463000064631000646320006463300064634000646350006463600064637000646380006463900064640000646410006464200064643000646440006464500064646000646470006464800064649000646500006465100064652000646530006465400064655000646560006465700064658000646590006466000064661000646620006466300064664000646650006466600064667000646680006466900064670000646710006467200064673000646740006467500064676000646770006467800064679000646800006468100064682000646830006468400064685000646860006468700064688000646890006469000064691000646920006469300064694000646950006469600064697000646980006469900064700000647010006470200064703000647040006470500064706000647070006470800064709000647100006471100064712000647130006471400064715000647160006471700064718000647190006472000064721000647220006472300064724000647250006472600064727000647280006472900064730000647310006473200064733000647340006473500064736000647370006473800064739000647400006474100064742000647430006474400064745000647460006474700064748000647490006475000064751000647520006475300064754000647550006475600064757000647580006475900064760000647610006476200064763000647640006476500064766000647670006476800064769000647700006477100064772000647730006477400064775000647760006477700064778000647790006478000064781000647820006478300064784000647850006478600064787000647880006478900064790000647910006479200064793000647940006479500064796000647970006479800064799000648000006480100064802000648030006480400064805000648060006480700064808000648090006481000064811000648120006481300064814000648150006481600064817000648180006481900064820000648210006482200064823000648240006482500064826000648270006482800064829000648300006483100064832000648330006483400064835000648360006483700064838000648390006484000064841000648420006484300064844000648450006484600064847000648480006484900064850000648510006485200064853000648540006485500064856000648570006485800064859000648600006486100064862000648630006486400064865000648660006486700064868000648690006487000064871000648720006487300064874000648750006487600064877000648780006487900064880000648810006488200064883000648840006488500064886000648870006488800064889000648900006489100064892000648930006489400064895000648960006489700064898000648990006490000064901000649020006490300064904000649050006490600064907000649080006490900064910000649110006491200064913000649140006491500064916000649170006491800064919000649200006492100064922000649230006492400064925000649260006492700064928000649290006493000064931000649320006493300064934000649350006493600064937000649380006493900064940000649410006494200064943000649440006494500064946000649470006494800064949000649500006495100064952000649530006495400064955000649560006495700064958000649590006496000064961000649620006496300064964000649650006496600064967000649680006496900064970000649710006497200064973000649740006497500064976000649770006497800064979000649800006498100064982000649830006498400064985000649860006498700064988000649890006499000064991000649920006499300064994000649950006499600064997000649980006499900065000000650010006500200065003000650040006500500065006000650070006500800065009000650100006501100065012000650130006501400065015000650160006501700065018000650190006502000065021000650220006502300065024000650250006502600065027000650280006502900065030000650310006503200065033000650340006503500065036000650370006503800065039000650400006504100065042000650430006504400065045000650460006504700065048000650490006505000065051000650520006505300065054000650550006505600065057000650580006505900065060000650610006506200065063000650640006506500065066000650670006506800065069000650700006507100065072000650730006507400065075000650760006507700065078000650790006508000065081000650820006508300065084000650850006508600065087000650880006508900065090000650910006509200065093000650940006509500065096000650970006509800065099000651000006510100065102000651030006510400065105000651060006510700065108000651090006511000065111000651120006511300065114000651150006511600065117000651180006511900065120000651210006512200065123000651240006512500065126000651270006512800065129000651300006513100065132000651330006513400065135000651360006513700065138000651390006514000065141000651420006514300065144000651450006514600065147000651480006514900065150000651510006515200065153000651540006515500065156000651570006515800065159000651600006516100065162000651630006516400065165000651660006516700065168000651690006517000065171000651720006517300065174000651750006517600065177000651780006517900065180000651810006518200065183000651840006518500065186000651870006518800065189000651900006519100065192000651930006519400065195000651960006519700065198000651990006520000065201000652020006520300065204000652050006520600065207000652080006520900065210000652110006521200065213000652140006521500065216000652170006521800065219000652200006522100065222000652230006522400065225000652260006522700065228000652290006523000065231000652320006523300065234000652350006523600065237000652380006523900065240000652410006524200065243000652440006524500065246000652470006524800065249000652500006525100065252000652530006525400065255000652560006525700065258000652590006526000065261000652620006526300065264000652650006526600065267000652680006526900065270000652710006527200065273000652740006527500065276000652770006527800065279000652800006528100065282000652830006528400065285000652860006528700065288000652890006529000065291000652920006529300065294000652950006529600065297000652980006529900065300000653010006530200065303000653040006530500065306000653070006530800065309000653100006531100065312000653130006531400065315000653160006531700065318000653190006532000065321000653220006532300065324000653250006532600065327000653280006532900065330000653310006533200065333000653340006533500065336000653370006533800065339000653400006534100065342000653430006534400065345000653460006534700065348000653490006535000065351000653520006535300065354000653550006535600065357000653580006535900065360000653610006536200065363000653640006536500065366000653670006536800065369000653700006537100065372000653730006537400065375000653760006537700065378000653790006538000065381000653820006538300065384000653850006538600065387000653880006538900065390', '1049'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #mytable OFF;

SET STATISTICS IO ON;

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

E2(n) AS(

SELECT a.n FROM E a, E b -- 100

),

E4(n) AS(

SELECT a.n FROM E2 a, E2 b -- 10K

),

cteTally(n) AS(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E4

)

SELECT m.id, m.ValueCount, SUBSTRING( m.ValueList, (n*8)-7, 8)

FROM #mytable m

JOIN cteTally t ON m.ValueCount >= t.n

--ORDER BY ID, n; -- not needed.

SELECT m.id, m.ValueCount, ns.token

FROM #mytable m

CROSS APPLY dbo.NSplit2B(m.ValueList,8,0) ns;

SET STATISTICS IO OFF;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Split string into multiple entries based upon known static length

Cookies on SQLServerCentral