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 Wednesday, June 19, 2013 10:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 5, 2014 4:32 PM
Points: 20, Visits: 36
Hi,

Need your help in accomplishing this one. We run on SQL Server 2008. I've a string that can be upto 200 characters max and after every 32 characters a pile (|) got to be inserted. if that 32 characters come in midword, then that pipe has to be placed in the beginning of that word, not in the middle of the word.

Example:

Original String: ABCDEFGH IJKLMNOPQRS TUVWXYZ ABCDEFGHIJKLMN OPQRSTUVWXYZA BCDEFGHIJKLMNOP

Want this way: ABCDEFGH IJKLMNOPQRS TUVWXYZ |ABCDEFGHIJKLMN OPQRSTUVWXYZA |BCDEFGHIJKLMNOP

since these strings are in a table.column and this need to operate on the entire data set (about 200k records), performance is also important.

thanks in advance..
Post #1465283
Posted Wednesday, June 19, 2013 3:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:01 AM
Points: 2,116, Visits: 6,441
So are you talking about updating the table once-off, or whenever a client app retrieves the data? Plus what if the previous "word" is less than 32 characters long and a pipe has been inserted, what do you do?


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1465396
Posted Wednesday, June 19, 2013 3:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 5, 2014 4:32 PM
Points: 20, Visits: 36
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.
Post #1465412
Posted Thursday, June 20, 2013 3:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:01 AM
Points: 2,116, Visits: 6,441
I'd strongly suggest you do this on the client side. SQL isn't geared towards manipulating strings.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1465546
Posted Thursday, June 20, 2013 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
)



“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 #1465749
Posted Friday, June 21, 2013 2:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:01 AM
Points: 2,116, Visits: 6,441
Now why does this InlineTally function look so familiar?

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1466086
Posted Friday, June 21, 2013 2:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
Jan Van der Eecken (6/21/2013)
Now why does this InlineTally function look so familiar?


Same reason as Feynman diagrams are now so familiar, Jan


“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 #1466091
Posted Friday, June 21, 2013 10:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:01 AM
Points: 2,116, Visits: 6,441
I like the Penguin one.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1466302
Posted Monday, June 24, 2013 2:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 5, 2014 4:32 PM
Points: 20, Visits: 36
Thanks you all guys for your responses. I'll implement this and let you know..
Post #1466912
Posted Monday, June 24, 2013 4:32 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
Venkata-433090 (6/24/2013)
Thanks you all guys for your responses. I'll implement this and let you know..


My question would be, what do you want to do with "words" that are more than 32 characters. Heh... don't say it won't happen.


--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 #1466943
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse