SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Append characters in a sentence after certain length


Append characters in a sentence after certain length

Author
Message
Venkata-433090
Venkata-433090
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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..
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3289 Visits: 6497
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)
Venkata-433090
Venkata-433090
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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.
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3289 Visits: 6497
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)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42428 Visits: 20012
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
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3289 Visits: 6497
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)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42428 Visits: 20012
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
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3289 Visits: 6497
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)
Venkata-433090
Venkata-433090
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 36
Thanks you all guys for your responses. I'll implement this and let you know..
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219622 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search