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
bitbucket-25253 (2/8/2010)
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?

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

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

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 haSmile].')

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

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: 5679 Visits: 25280
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 haSmile].')
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.

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
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
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 haSmile#].'
)
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

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44778 Visits: 39845
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. :-D

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 haSmile].'

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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
Thanks Jeff
your PATINDEX idea seems a step closer to a dream 'WildReplace' function which might be :

CREATE FUNCTION dbo.fn_WildReplace(@SomeText varchar(max),
@Pattern varchar(500), @ReplaceWith varchar(max))
.....


Edit: corrected syntax

Usage :

SELECT dbo.fn_WildReplace('Without a doubt SQLCentral is 
home to finest SQL folk[comment:#grovel].', '%[comment:#%]', '')
--returning:
--Without a doubt SQLCentral is home to finest SQL folk.




The function doesn't specify any start or end markers, just a pattern to find and replace.

I can see we can use PATINDEX to find start location of the pattern, but I canny figure out how you would determine the end location of the pattern?

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
BTW I figured also if passing pattern to dream function then I would have to specify additional charaters to avoid clash with meta characters

'%[[]comment:#%[]]'



where [[] and []] represent single characters '[' and ']'

C# Gnu
____________________________________________________


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

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44778 Visits: 39845
C# Screw (2/9/2010)
BTW I figured also if passing pattern to dream function then I would have to specify additional charaters to avoid clash with meta characters

'%[[]comment:#%[]]'



where [[] and []] represent single characters '[' and ']'


You don't need (or want) the []].... only the [[]. Look in Books Online under "Like".

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44778 Visits: 39845
C# Screw (2/9/2010)
Thanks Jeff
your PATINDEX idea seems a step closer to a dream 'WildReplace' function which might be :

CREATE FUNCTION dbo.fn_WildReplace(@SomeText varchar(max),
@Pattern varchar(500), @ReplaceWith varchar(max))
.....


Edit: corrected syntax

Usage :

SELECT dbo.fn_WildReplace('Without a doubt SQLCentral is 
home to finest SQL folk[comment:#grovel].', '%[comment:#%]', '')
--returning:
--Without a doubt SQLCentral is home to finest SQL folk.




The function doesn't specify any start or end markers, just a pattern to find and replace.

I can see we can use PATINDEX to find start location of the pattern, but I canny figure out how you would determine the end location of the pattern?


It's easy if you want to do that. Just do a split on the % in a single pattern to replace the left and right patterns. I was just being a bit lazy... didn't want to have to worry about writing code to escape the % if someone needed to look for it.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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
Thanks Jeff

appologies for the delay...

When I get a moment I might try and build a function that fully supports the PATINDEX sytax.

C# Gnu
____________________________________________________


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

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