Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Append characters in a sentence after certain length Expand / Collapse
Author
Message
Posted Monday, June 24, 2013 4:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 35,769, Visits: 32,432
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))



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1466944
Posted Tuesday, June 25, 2013 1:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 2:05 PM
Points: 2, Visits: 6
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION String_Insert ( @String VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @i INT= 0
DECLARE @x INT= 0
DECLARE @MAX INT = LEN(LTRIM(RTRIM(@String)))

DECLARE @REV VARCHAR(32)
SET @i = 32;
WHILE @i <= @MAX
BEGIN

IF SUBSTRING(@String, @i, 1) = ' '
BEGIN

SET @String = STUFF(@String, @i, 0, '|')
END
ELSE
BEGIN
SET @x = CHARINDEX(' ',
REVERSE(SUBSTRING(@String, 1, @i)))
SET @i = @i - @x + 2;
SET @String = STUFF(@String, @i, 0, '|')
END

SET @i = @i + 32;
END

-- Return the result of the function
RETURN @String


END
GO


Here I created a temp table and tested on it.

IF object_ID('tempDB..#temp') IS NOT NULL
DROP TABLE #temp

CREATE TABLE #temp
(
String VARCHAR(200)
)

INSERT #temp (String) VALUES ( 'ABCDEFGH IJKLMNOPQRS TUVWXYZ ABCDEFGHIJKLMN OPQRSTUVWXYZA BCDEFGHIJKLMNOP'
),('ABCDEFGH IJKLMNOPQRS TUVWXYZ ABCDEFGHIJKLMN OPQRSTUVWXYZAB CDEFGHIJKLMNOP')

SELECT String_Insert(String) FROM #temp

Thanks to my friend Lakshmi who helped me in writing the code.This is one way of doing it! Hope it works.
Post #1467370
Posted Tuesday, June 25, 2013 1:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 2:05 PM
Points: 2, Visits: 6
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION String_Insert ( @String VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @i INT= 0
DECLARE @x INT= 0
DECLARE @MAX INT = LEN(LTRIM(RTRIM(@String)))

DECLARE @REV VARCHAR(32)
SET @i = 32;
WHILE @i <= @MAX
BEGIN

IF SUBSTRING(@String, @i, 1) = ' '
BEGIN

SET @String = STUFF(@String, @i, 0, '|')
END
ELSE
BEGIN
SET @x = CHARINDEX(' ',
REVERSE(SUBSTRING(@String, 1, @i)))
SET @i = @i - @x + 2;
SET @String = STUFF(@String, @i, 0, '|')
END

SET @i = @i + 32;
END

-- Return the result of the function
RETURN @String


END
GO


Here I created a temp table and tested on it.

IF object_ID('tempDB..#temp') IS NOT NULL
DROP TABLE #temp

CREATE TABLE #temp
(
String VARCHAR(200)
)

INSERT #temp (String) VALUES ( 'ABCDEFGH IJKLMNOPQRS TUVWXYZ ABCDEFGHIJKLMN OPQRSTUVWXYZA BCDEFGHIJKLMNOP'
),('ABCDEFGH IJKLMNOPQRS TUVWXYZ ABCDEFGHIJKLMN OPQRSTUVWXYZAB CDEFGHIJKLMNOP')

SELECT String_Insert(String) FROM #temp


This is one way of doing it! Hope it works.
Post #1467373
Posted Tuesday, June 25, 2013 5:56 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:18 PM
Points: 1,816, Visits: 5,910
Here is an iTVF for it...


CREATE FUNCTION SplitWithDelimiter (@MyString VARCHAR(200), @MaxLength INT, @Delimiter CHAR(1), @Breakers varchar(256))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN

SELECT substring(base.value, 1, Pos1) + @Delimiter + substring(base.value, Pos1 + 1, Pos2 - Pos1) + @Delimiter + substring(base.value, Pos2 + 1, Pos3 - Pos2) + @Delimiter + substring(base.value, Pos3 + 1, Pos4 - Pos3) + @Delimiter + substring(base.value, Pos4 + 1, Pos5 - Pos4) + @Delimiter + substring(base.value, Pos5 + 1, Pos6 - Pos5) + @Delimiter + substring(base.value, Pos6 + 1, Pos7 - Pos6) AS result
FROM (
VALUES (@MyString)
) AS base(value)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, 1, @MaxLength))), (@MaxLength + 1)), @MaxLength)
) a(Pos1)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos1 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos1
) b(Pos2)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos2 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos2
) c(Pos3)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos3 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos3
) d(Pos4)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos4 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos4
) e(Pos5)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos5 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos5
) f(Pos6)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos6 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos6
) g(Pos7)

I don't claim it is the quickest, but it is quite flexible:

Parameters:

@MyString : The data to be manipulated
@MaxLength : The maximum length of each new "chunk" of data
@Delimiter : The character used to seperate the new "chunks"
@Breakers : Pattern used to find a suitable break in the data - can be as simple as ' ' or more complicated like '[ .,?!:;)]'


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1467411
    Posted Tuesday, June 25, 2013 8:46 PM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Wednesday, December 10, 2014 12:17 PM
    Points: 1,095, Visits: 3,182
    Here's a recursive CTE method as a iTVF that could work.

    I would like to come up with a none recursive method, but brain fade is setting in and I'm not seeing a way to do it currently

    One thing to note is that it will break if there is a word longer than the @maxlen specified. The character at that point will be replaced.

    CREATE FUNCTION splitAt(@str varchar(8000), @maxlen int, @splitOn varchar(1), @replaceWith varchar(1)) RETURNS TABLE AS
    RETURN
    WITH rcte as (
    SELECT substring(@str,0,@maxlen - charindex(@spliton,reverse(substring(@str,0,@maxlen)))) s,
    substring(@str,@maxlen - charindex(@spliton,reverse(substring(@str,0,@maxlen))) + 1,8000) r
    UNION ALL
    SELECT substring(r,0,@maxlen - charindex(@spliton,reverse(substring(r,0,@maxlen)))) s,
    substring(r,@maxlen - charindex(@spliton,reverse(substring(r,0,@maxlen))) + 1,8000) r
    FROM rcte
    WHERE len(r) > 0
    )
    SELECT SUBSTRING(S,2,8000) splitResult
    FROM (
    SELECT @replacewith + s AS [text()]
    FROM rCte
    ORDER BY LEN(r) DESC
    FOR XML PATH ('')) A (S)

    On my machine with 10000 address rows with an average length of 55 (max 121, min 21) it took around a second.
    Post #1467424
    Posted Thursday, June 27, 2013 2:22 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: 2 days ago @ 2:14 AM
    Points: 6,890, Visits: 14,253
    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(
    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 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
    Exploring Recursive CTEs by Example Dwain Camps
    Post #1468009
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse