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 9:06 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:13 AM
Points: 225, Visits: 643
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
____________________________________________________


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


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:13 AM
Points: 225, Visits: 643
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
____________________________________________________


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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 5,571, Visits: 24,767
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.


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 #861885
Posted Monday, February 8, 2010 11:16 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:13 AM
Points: 225, Visits: 643
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #861907
Posted Monday, February 8, 2010 11:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #862220
Posted Tuesday, February 9, 2010 2:31 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:13 AM
Points: 225, Visits: 643
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #862284
Posted Tuesday, February 9, 2010 2:53 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:13 AM
Points: 225, Visits: 643
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #862293
Posted Tuesday, February 9, 2010 5:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #862356
Posted Tuesday, February 9, 2010 5:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #862359
Posted Tuesday, February 16, 2010 9:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:13 AM
Points: 225, Visits: 643
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #866199
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse