Split a variable length string without using a WHILE loop

Question

Split a variable length string without using a WHILE loop

Melanie Peterson

SSCrazy

Points: 2387
More actions
December 23, 2013 at 2:24 pm

#302581

I have a field in a table where the data varies in length. For some records, the length of the field is 8, for some records it's 20, for many records it's some number in between, NOT NECESSARILY divisible by four. I've been asked to change this data to xxxx.xxxx.xxxx. etc until I run out of numbers. So, for example if I have 12345678, I should change it to 1234.5678. If I have 12345, I should change it to 1234.5. 1234567890ABCDEFGH becomes 1234.5678.90AB.CDEF.GH. I can do this with a WHILE loop, but I try to stay away from looping in T-SQL and use set logic instead. I'm wondering, is there a way to do this that doesn't involve loops?

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

check out this thread that has both a scalar and an inline table value function that resolves the same issue: splitting a string on a delimiter into new columns:

http://www.sqlservercentral.com/Forums/Topic1524275-3077-1.aspx#bm1524325

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 2

Thanks Lowell - this function would work if I had a delimiter, but I don't. I just need to break the string every 4 characters by inserting a period, until I get down to 4 characters or less, and then just add them.

Lowell SSC Guru Points: 323518 More actions · Answer 3

whoops after reading your post AFTER i posted, i see you really want to chop up the string based on a fixed length(ie 4 chars)

well, i know an example of that too! just change the length in this example and it should help as well:

http://www.sqlservercentral.com/Forums/Topic1525433-391-1.aspx#bm1525498

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 4

Ah ha! This looks good. It's almost 5:30 PM here, so I'm going to look at this tomorrow and let you know if it helps me, but it looks good. Thanks very much!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

Certainly no need to use a WHILE loop here. This should do it for you:

WITH SampleData (MyStr) AS

(

SELECT '12345678'

UNION ALL SELECT '12345'

UNION ALL SELECT '1234567890ABCDEFGH '

),

Tally (n) AS

(

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

FROM sys.all_columns

),

StringsSplit AS

(

SELECT MyStr, a=SUBSTRING(a.MyStr, 4*n-3, 4), n

FROM SampleData a

CROSS APPLY

(

SELECT n

FROM Tally

WHERE n BETWEEN 1 AND 1+LEN(MyStr)/4

) b

)

SELECT MyStr

,NewStr=STUFF((

SELECT '.' + a

FROM StringsSplit c

WHERE a.MyStr = c.MyStr

ORDER BY n

FOR XML PATH('')

), 1, 1, '')

FROM SampleData a;

If you're not familiar with using a Tally table, Google it and you should find plenty of info.

Basically what I did was to split the strings on each 4 character boundary point with the Tally table and then combined them back together using FOR XML PATH.

FOR XML PATH is explained here: Creating a comma-separated list[/url] by Wayne Sheffield.

Merrry Christmas!

Edit: Forgot to mention. 2000 in the TOP clause of the Tally is based your data being VARCHAR(8000). You can reduce it to one fourth of the max length of your string.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

ScottPletcher SSC Guru Points: 101092 More actions · Answer 6

If you don't have any data longer than 24 bytes (or so), it might be easier just to "brute STUFF" it:

WITH SampleData (MyStr) AS

(

SELECT '12345678'

UNION ALL SELECT '12345'

UNION ALL SELECT '1234567890ABCDEFGH'

UNION ALL SELECT '1234567890ABCDEFGHIJK'

)

SELECT

MyStr,

CASE WHEN LEN(MyStr) < 05 THEN MyStr

WHEN LEN(MyStr) < 09 THEN STUFF(MyStr, 5, 0, '.')

WHEN LEN(MyStr) < 13 THEN STUFF(STUFF(MyStr, 5, 0, '.'), 10, 0, '.')

WHEN LEN(MyStr) < 17 THEN STUFF(STUFF(STUFF(MyStr, 5, 0, '.'), 10, 0, '.'), 15, 0, '.')

WHEN LEN(MyStr) < 21 THEN STUFF(STUFF(STUFF(STUFF(MyStr, 5, 0, '.'), 10, 0, '.'), 15, 0, '.'), 20, 0, '.')

ELSE STUFF(STUFF(STUFF(STUFF(STUFF(MyStr, 5, 0, '.'), 10, 0, '.'), 15, 0, '.'), 20, 0, '.'), 25, 0, '.')

END

FROM SampleData

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 7

ScottPletcher (12/24/2013)
If you don't have any data longer than 24 bytes (or so), it might be easier just to "brute STUFF" it:

Awesome! I don't believe anything will come close to touching that for speed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101092 More actions · Answer 8

Jeff Moden (12/24/2013)
ScottPletcher (12/24/2013)
If you don't have any data longer than 24 bytes (or so), it might be easier just to "brute STUFF" it:
Awesome! I don't believe anything will come close to touching that for speed.

Thanks :blush:

The really cool part was getting to legitimately use ' "brute STUFF" it' in a sentence ;-).

Edit: Corrected typo.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 9

ScottPletcher (12/24/2013)
Jeff Moden (12/24/2013)
ScottPletcher (12/24/2013)
If you don't have any data longer than 24 bytes (or so), it might be easier just to "brute STUFF" it:
Awesome! I don't believe anything will come close to touching that for speed.
Thanks :blush:
The really cool part was getting to legitimately use ' "brute STUFF" it' in a sentence ;-).
Edit: Corrected typo.

BWAAA_HAAA!!!! I know what you mean! I'm not one to use Hungarian Notation but I couldn't resist the temptation to use it on a "Tally" function so that I could admonish folks, "Well, if you had used the fn_Tally function I wrote for you, you wouldn't be in such a pickle" and not have it be an HR violation. 😀

Merry Christmas and Happy New Year to you and yours, Scott.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 10

Jeff Moden (12/24/2013)
Well, if you had used the fn_Tally function I wrote for you, you wouldn't be in such a pickle

That took more than a minute to sink in but I finally got it while drinking my coffee, causing two streams of dark liquid to be expelled noisily from my nostrils.

Merry Christmas to all!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 11

I apologize for not responding to this for so long. I got dragged away to a different project and in the meanwhile this issue became a moot point. I appreciate everyone's help anyway. I've marked the "Brute Stuff" (hee hee) answer as "the answer" because I think it would've worked the best for my case. Thanks everyone!