Jeff Moden (6/24/2013)

---

ChrisM@Work (6/20/2013)

---

Venkata-433090 (6/19/2013)

---

Thanks for your response. Its kind of when the client retrieves the data.

again, the 32 character count should start from where the pipe was placed.

Shouldn't take too much effort to tailor this to your requirements:

-- sample data

;WITH SampleData AS (

SELECT

MyString = RTRIM(STUFF(STUFF(MyString,32,0,'|'),64,0,'|'))

FROM (

SELECT MyString = CAST('ABCDEFGH IJKLMNOPQRS TUVWXYZ ABCDEFGHIJKLMN OPQRSTUVWXYZA BCDEFGHIJKLMNOP' AS VARCHAR(200))

) d

)

-- solution

SELECT

MyString,

NewString =

STUFF(

STUFF(

STUFF(

STUFF(

STUFF(

STUFF(

CleanString

,Pos1,0,CASE WHEN Pos1 > 1 THEN '|' ELSE '' END)

,Pos2,0,CASE WHEN Pos2 > 1 THEN '|' ELSE '' END)

,Pos3,0,CASE WHEN Pos3 > 1 THEN '|' ELSE '' END)

,Pos4,0,CASE WHEN Pos4 > 1 THEN '|' ELSE '' END)

,Pos5,0,CASE WHEN Pos5 > 1 THEN '|' ELSE '' END)

,Pos6,0,CASE WHEN Pos6 > 1 THEN '|' ELSE '' END)

FROM SampleData

CROSS APPLY (SELECT CleanString = REPLACE(MyString,'|','')) y

CROSS APPLY ( -- get the position of the last ' ' in each 32 character 'line'

SELECT

Pos1 = MAX(CASE WHEN line = 1 THEN LastSpaceInLine+1 ELSE 1 END),

Pos2 = MAX(CASE WHEN line = 2 THEN LastSpaceInLine+1 ELSE 1 END),

Pos3 = MAX(CASE WHEN line = 3 THEN LastSpaceInLine+1 ELSE 1 END),

Pos4 = MAX(CASE WHEN line = 4 THEN LastSpaceInLine+1 ELSE 1 END),

Pos5 = MAX(CASE WHEN line = 5 THEN LastSpaceInLine+1 ELSE 1 END),

Pos6 = MAX(CASE WHEN line = 6 THEN LastSpaceInLine+1 ELSE 1 END)

FROM (

SELECT

x.line,

LastSpaceInLine = MAX(n)

FROM dbo.InlineTally (200) t

CROSS APPLY (SELECT line = 1+n/32) x

WHERE n <= LEN(MyString)

AND SUBSTRING(MyString,t.n,1) = ' '

GROUP BY x.line

) d

) x1

-- Function dbo.InlineTally

CREATE FUNCTION [dbo].[InlineTally]

(@RowCount INT)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

(

WITH E1(N) AS ( -- 10 rows

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

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c), -- 10 x 10 x 10 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b) --1000 x 1000 rows max

SELECT TOP (@RowCount) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

)

Very cool but try it with the following replacement and see what happens with "zz".

SELECT MyString = CAST('aa bb cc dd ee ff gg hh ii jj kk ll mm nn oo pp qq rr ss tt uu vv ww xx yy zz' AS VARCHAR(200))