May 22, 2014 at 2:57 pm
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) + <br>
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 '<br>' and strip them.
Is there a cleaner (and clearer) way of doing this?
May 22, 2014 at 5:17 pm
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 '< br>' + SUBSTRING(@str, n, 10)
FROM cteTally
ORDER BY n
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,4,'')
--SELECT '< br>' + 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.
May 23, 2014 at 7:27 am
And looking for just
is probably not good enough depending on the source of your data. You also need to find <br /> and <br/>
_______________________________________________________________
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/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy