Append characters in a sentence after certain length

Question

Append characters in a sentence after certain length

Viewing post 16 (of 15 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

Ah, of course - thanks Jeff, good spot. Here's a fix:

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

UNION ALL SELECT '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'

) d

)

-- solution

SELECT

MyString,

NewString =

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 WHOLE 32 character 'line'

SELECT

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

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

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

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

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

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

FROM (

SELECT

x.LineNum,

LastSpaceInLine = MAX(n)

FROM dbo.InlineTally (200) t

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

WHERE n <= LEN(CleanString)/32*32

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

GROUP BY x.LineNum

) d

) x1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden