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 123»»»

WILD REPLACE ? Expand / Collapse
Author
Message
Posted Monday, February 8, 2010 7:51 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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 ha:)].

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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #861682
Posted Monday, February 8, 2010 8:06 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #861701
Posted Monday, February 8, 2010 8:17 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #861712
Posted Monday, February 8, 2010 8:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:44 PM
Points: 12,904, Visits: 31,978
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 ha:)].'

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #861721
Posted Monday, February 8, 2010 8:27 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
Hi Lowell
yes replace with same text - nothing in example:

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



C# Gnu
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #861723
Posted Monday, February 8, 2010 8:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:44 PM
Points: 12,904, Visits: 31,978
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #861725
Posted Monday, February 8, 2010 8:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #861727
Posted Monday, February 8, 2010 8:32 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #861733
Posted Monday, February 8, 2010 8:39 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #861742
Posted Monday, February 8, 2010 8:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 5,586, Visits: 24,935
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
Post #861766
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse