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) + <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?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 22, 2014 at 5:17 pm

  #1715907

  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.

  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: 286602

  More actions

  May 23, 2014 at 7:27 am

  #1716051

  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/