Insert a breaking character into a string at regular intervals

Question

Post reply

Insert a breaking character into a string at regular intervals

webskater

SSCarpal Tunnel

Points: 4214
More actions
May 22, 2014 at 2:57 pm

#289148

DECLARE @STR varchar(255)
SET @STR = 'ABCD1234EFGH5678MARWOKELL85674INTE42569'
In need to break that string up and store it so it stored like this:
ABCD1234EF
GH5678MARW
OKELL85674
INTE42569
i.e. I need to put the html for a soft return in every 10th character.
So, I have come up with this ...
DECLARE @STR varchar(100), @str1 varchar(120) = ''
SET @STR = '11111111112222222222333333333344444444445555555555666666666'
while LEN(@str) > 1
BEGIN
IF LEN(@str) >= 10
BEGIN
SET @str1 = @str1 + LEFT(@str, 10) + 
END
ELSE
BEGIN
SET @str1 = @str1 + @STR
END
IF LEN(@str) > 10
BEGIN
SET @STR = RIGHT(@str, LEN(@str) - 10)
END
ELSE
BEGIN
BREAK
END
END
That seems pretty crude and I still have to test to see if the last four characters are ' ' and strip them.
Is there a cleaner (and clearer) way of doing this?

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

Would something like this help?

I included some commented code to help you to test.

DECLARE @STR varchar(100), @str1 varchar(120) = ''

SET @STR = '11111111112222222222333333333344444444445555555555666666666';

WITH E1(n) AS(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

),

cteTally(n) AS(

SELECT TOP( (LEN(@str) / 10) + 1) --Use only the rows that we need.

(ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) * 10) - 9 --Sequence for 1,11,21,31...

FROM E1 a, E1 b -- 10 x 10 = 100 rows

)

SELECT STUFF((SELECT ' ' + SUBSTRING(@str, n, 10)

FROM cteTally

ORDER BY n

FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,4,'')

--SELECT ' ' + SUBSTRING(@str, n, 10)

-- FROM cteTally

-- ORDER BY n

References:

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

http://www.sqlservercentral.com/articles/T-SQL/62867/

EDIT: Be sure to remove the space that I had to add in the tag.

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

And looking for just

is probably not good enough depending on the source of your data. You also need to find and

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/