Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


WILD REPLACE ?


WILD REPLACE ?

Author
Message
C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
Dear Friends

I need to replace blocks of text between a couple of markers.

Sample text :

"some text here [comment:# Variable text] and some more text here"

I would like to replace "[comment:# Variable text]" with nothing.

Note: 'Variable text' will be be different throught the text.

I am imagining an SQL function that might accept wild cards perhaps like this:

UPDATE MyTable 
SET MyText = dbo.fn_WildReplace(MyText ,'[comment:#%]','')


[Edit: amended sytax]

In my case the opening and closong brackets will be in the text, and are not meant to represent any wild card expression format.

I was wondering if anyone had come across/coded a sulution to such a challenge ..?

Or perhaps you have an easier solution ..?


Here is a larger test text:

Without a doubt SQLCentral is home to finest SQL folk[comment:#grovel].
Sometimes things may get heated[comment:#super hot debate].
But great minds find their way[comment:#method to madness?].
The rest of us eat Pork Chops[comment:#ha haSmile].

Should end up as :
Without a doubt SQLCentral is home to finest SQL folk.
Sometimes things may get heated.
But great minds find their way.
The rest of us eat Pork Chops.

#Screw

C# Gnu
____________________________________________________


Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
Screw: Is there some reason that you are not using the code tags? I am pretty sure that I have mentioned this to you before.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
Hmm, I apologize, now I am not sure that I have mentioned it to you before. Sorry ...

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14933 Visits: 38926
Can you explain a little more?
if i had THIS as a comment:

declare @text varchar(4000)
SET @text = Without a doubt SQLCentral is home to finest SQL folk[comment:#grovel].
Sometimes things may get heated[comment:#super hot debate].
But great minds find their way[comment:#method to madness?].
The rest of us eat Pork Chops[comment:#ha haSmile].'



do you want to replace all 4 of the comments betweent he brackets with the SAME replacement text?
from your example, you are just replacing them with an empty string instead?
it's fairly easy to do, you use a tally table, and find the start and end brackets, then use STUFF to replace the contents. between the two brackets.

here's a simple way using a WHILE Loop:

--===== Replace all [' and ']' pairs with nothing
WHILE CHARINDEX('[',@text ) > 0
SELECT @text = STUFF(@text ,
CHARINDEX('[',@text ),
CHARINDEX(']',@text ) - CHARINDEX('[',@text ) + 1, --1 is the length of the search term
'')



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
Hi Lowell
yes replace with same text - nothing in example:

UPDATE MyTable 
SET MyText = dbo.fn_WildReplace(MyText ,'[comment:#%]','')



C# Gnu
____________________________________________________


Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14933 Visits: 38926
you replied while i was editing my original post;
here's one way; i'm testing a Tally solution now:

here's a simple way using a WHILE Loop:

--===== Replace all [' and ']' pairs with nothing
WHILE CHARINDEX('[',@text ) > 0
SELECT @text = STUFF(@text ,
CHARINDEX('[',@text ),
CHARINDEX(']',@text ) - CHARINDEX('[',@text ) + 1, --1 is the length of the search term
'')



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
RBarryYoung (2/8/2010)
Hmm, I apologize, now I am not sure that I have mentioned it to you before. Sorry ...

Thats OK Barry no worries - i did miss codes off the SQL

C# Gnu
____________________________________________________


Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
Lowell (2/8/2010)
you replied while i was editing my original post;
here's one way; i'm testing a Tally solution now:

here's a simple way using a WHILE Loop:

--===== Replace all [' and ']' pairs with nothing
WHILE CHARINDEX('[',@text ) > 0
SELECT @text = STUFF(@text ,
CHARINDEX('[',@text ),
CHARINDEX(']',@text ) - CHARINDEX('[',@text ) + 1, --1 is the length of the search term
'')


Hi Lowell
I guess the start marker will be '[comment:#' end marker ']' if that helps.

C# Gnu
____________________________________________________


Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
There will be other code between square brackets in the text such as

"Some code between markers [SQL:SELECT....] needs to stay."
The above example executes some SQL.

Its just the "[Comment:# ...]" sections I need to pull out.


So :

"Test text created [SQL:SELECT GetDate()] is here[COMMENT:#hello world]."

becomes

"Test text created [SQL:SELECT GetDate()] is here."

C# Gnu
____________________________________________________


Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
Without using a Tally table
DECLARE @What VARCHAR(200)
DECLARE @Start INT
DECLARE @End Int
SET @What = 'Sometimes things may get [SQL GETDATE()] heated[comment:#super hot debate].'
SET @Start = CHARINDEX('[COMMENT',@What,1)
SET @End = CHARINDEX(']',@What,@Start+1)
SELECT @Start,@End
SELECT SUBSTRING(@What,1,@Start-1) + SUBSTRING(@What,@Start+1,LEN(@What)-(@End +1))
Result:
Sometimes things may get [SQL GETDATE()] heated



Is this what you are looking for?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
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