WILD REPLACE ?
-
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 MyTableSET 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
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Hmm, I apologize, now I am not sure that I have mentioned it to you before. Sorry ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
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
--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! -
Hi Lowell
yes replace with same text - nothing in example:
UPDATE MyTableSET MyText = dbo.fn_WildReplace(MyText ,'[comment:#%]','')
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
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! -
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 -
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 -
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 -
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?
-
bitbucket-25253 (2/8/2010)
Without using a Tally tableDECLARE @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?
Hi Ron
kind of yes ... but needs to run in an SQL SELECT statement as there will be multiple occurences of [comment#...] in the same varchar(max) text column.
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
CREATE TABLE #tTestData (MyTextCol VARCHAR(max))
INSERT INTO #tTestData
( MyTextCol )
VALUES ( 'Without a doubt SQLCentral is home to finest SQL folk[comment:#grovel].
Sometimes [SQL:SELECT GetDate()]things may get heated[comment:#super hot debate].
But [SQL:SELECT GetDate()]great minds find their way[comment:#method to madness?].
The rest of us eat Pork Chops[comment:#ha ha:)].')
DROP TABLE #tTestData
Should end up as :
'Without a doubt SQLCentral is home to finest SQL folk.
Sometimes [SQL:SELECT GetDate()]things may get heated.
But [SQL:SELECT GetDate()]great minds find their way.
The rest of us eat Pork Chops.'
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Again without a Tally table (hence not the most efficient method), but here goes.
CREATE TABLE #tTestData (MyTextCol VARCHAR(max))INSERT INTO #tTestData( MyTextCol )
VALUES ( 'Without a doubt SQLCentral is home to finest SQL folk[comment:#grovel].
Sometimes [SQL:SELECT GetDate()]things may get heated[comment:#super hot debate].
But [SQL:SELECT GetDate()]great minds find their way[comment:#method to madness?].
The rest of us eat Pork Chops[comment:#ha ha:)].')
DECLARE @What VARCHAR(MAX)
DECLARE @Part VARCHAR(Max)
DECLARE @Start INT
DECLARE @End Int
SET @What = (SELECT MyTextCol FROM #tTestData)
SET @End = 0
WHILE @End < LEN(@WHAT)
BEGIN
SET @Start = CHARINDEX('[COMMENT',@What,@End + 1)
SET @End = CHARINDEX(']',@What,@Start+1)
SET @What = SUBSTRING(@What,1,@Start-1) + SUBSTRING(@What,@End+1,LEN(@What))
CONTINUE
END
SELECT @What
Returns:
Without a doubt SQLCentral is home to finest SQL folk.
Sometimes [SQL:SELECT GetDate()]things may get heated.
But [SQL:SELECT GetDate()]great minds find their way.
The rest of us eat Pork Chops.
(1 row(s) affected)
Hope this get you started on a more efficient method, but it is the best I can do for now.
-
Thank you -
I figured I was in Tag/end tag territory, rather like HTML markers.
I had ago at creating a function :
--------------------------------- create some test data
CREATE TABLE #tTestData ( MyTextCol VARCHAR(MAX) )
INSERT INTO #tTestData
( MyTextCol
)
VALUES ( 'Without a doubt SQLCentral is home to finest SQL folk[comment:#grovel#].
Sometimes [SQL:SELECT GetDate()]things may get heated[comment:#super hot debate#].
But [SQL:SELECT GetDate()]great minds find their way[comment:#method to madness?#].
The rest of us eat Pork Chops[comment:#ha ha:)#].'
)
GO
-------------------------------
-- function
-- Brigzy 08-02-10
CREATE FUNCTION fn_STUFF_Between_Tags
(
@SomeText VARCHAR(MAX) ,
@StartTag VARCHAR(400),
@EndTag VARCHAR(400),
@StuffWith VARCHAR(400)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
WHILE CHARINDEX(@StartTag,@SomeText ) > 0 AND CHARINDEX(@EndTag,@SomeText ) >0
SELECT @SomeText = ISNULL(
STUFF(@SomeText , -- Start with this text
CHARINDEX(@StartTag,@SomeText ), -- Stuff from Start tag location
-- Stuff length = end tag location - start tag location + end tag length
CHARINDEX(@EndTag,@SomeText, /* look for end tag after start location->*/
CHARINDEX(@StartTag,@SomeText ) )
- CHARINDEX(@StartTag,@SomeText ) --
+ DATALENGTH(@EndTag),
@StuffWith)
,'')
RETURN @SomeText
END
GO
-------------------------------
-- Test it
SELECT dbo.fn_STUFF_Between_Tags(MyTextCol, '[COMMENT:#',']','')
FROM #tTestData
GO
-------------------------------
-- clean up
DROP FUNCTION fn_STUFF_Between_Tags
DROP TABLE #tTestData
I think though it is very RBAR - and needs a Jeff Tally table conversion
[Edit:just like you say BitBucket]...
[Edit: special thanks Lowell this is based on your SQL]
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
When using STUFF, the Tally table doesn't work so well for performance. Remember that although is has a ton of uses, it's not a panacea for replacing RBAR. This is one of those very rare places where RBAR actually works pretty well which, I guess, qualifies it as RBR instead. 😀
I believe the following may be what you're looking for.
CREATE FUNCTION dbo.PatternClean
(
@pString VARCHAR(MAX),
@pPatternLeft VARCHAR(MAX),
@pPatternRight VARCHAR(MAX)
)
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @IncorrectCharLoc INT,
@PatternFull VARCHAR(MAX),
@PatternRightLen INT
SELECT @PatternFull = REPLACE('%' + @pPatternLeft + '%' + @pPatternRight + '%','[','[[]'),
@PatternRightLen = LEN(@pPatternRight),
@IncorrectCharLoc = PATINDEX(@PatternFull, @pString)
WHILE @IncorrectCharLoc > 0
SELECT @pString = STUFF(@pString,
@IncorrectCharLoc,
CHARINDEX(@pPatternRight,@pString, @IncorrectCharLoc+1)
- @IncorrectCharLoc + @PatternRightLen, ''),
@IncorrectCharLoc = PATINDEX(@PatternFull, @pString)
RETURN @pString
END
GO
The thing that makes the code so fast is that fact that CHARINDEX and PATINDEX are use just once to find each pattern.
Here's your example of usage... of course you can use SELECT instead of PRINT. I just did that for visual effect.
DECLARE @LongString VARCHAR(MAX)SELECT @LongString =
'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:)].'
PRINT @LongString
PRINT ''
PRINT dbo.PatternClean(@LongString,'[comment:#',']')
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply