InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 10:50:07 GMT20http://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxIt does sound like fun. Thanks for the feedback. :-)Tue, 16 Feb 2010 18:34:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspx[quote][b]Jeff Moden (2/16/2010)[/b][hr][quote][b]C# Screw (2/16/2010)[/b][hr]Thanks Jeffappologies for the delay...When I get a moment I might try and build a function that fully supports the PATINDEX sytax.[/quote]I agree with Barry on this... a CLR UDF would probably be the right thing to do here... of course, any DB that requires a Wild Card Replace might need some of that C4 in a redesign process. :-D[/quote]We have varchar fields that contain SQL code, many thousands of rows.The database design is very cool indeed, consists of questions and answers with sql code embedded in both questions and answers supporting the business logic. Different answers send the user off to other questions depending on underlying data etc.Maintaining it though can be interesting!, being able to search and replace is helpful. I achieved it ok with the function I coded/posted earlier fn_STUFF_Between_Tags :cool: but supporting patindex style would be cooler :cool::cool:Tue, 16 Feb 2010 14:35:14 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspx[quote][b]C# Screw (2/16/2010)[/b][hr]Thanks Jeffappologies for the delay...When I get a moment I might try and build a function that fully supports the PATINDEX sytax.[/quote]I agree with Barry on this... a CLR UDF would probably be the right thing to do here... of course, any DB that requires a Wild Card Replace might need some of that C4 in a redesign process. :-DTue, 16 Feb 2010 13:10:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxCool! not Channel 4 then !! :-)Tue, 16 Feb 2010 11:37:40 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspx[quote][b]C# Screw (2/16/2010)[/b][hr](Hi Barry I am wondering what is C4?)[/quote][url=http://en.wikipedia.org/wiki/C-4_(explosive)]C4[/url] is a plastic explosive with a particularly high "burn rate" or (more properly) detonation velocity of 28,900 kph.Tue, 16 Feb 2010 11:34:17 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxWell I am reluctant to post this but what the heck - the result of a late night brainwave :I thought I had cracked it by finding the end position of the pattern by reversing the string and reversing the search pattern.It does in fact work if the search pattern only contains % characters.I was so jubilent - but then despondent as I realised that reversing something like '[a-b]' gives a pattern of ']b-a['. :w00t:At that point I gave up & went to bed !Here is the late night night code for what its worth:(Hi Barry I am wondering what is C4?)[code="sql"]ALTER PROCEDURE [dbo].[up_WildReplace] ( @Text VARCHAR(MAX) , @Pattern VARCHAR(500) , @ReplaceWithText VARCHAR(MAX) )---- c#Screw 09-02-10-- Search @SomeText for text matching @Pattern-- Replace all occurances of patterb with ReplaceWithText-- Designed so @Pattern can contain PATINDEX meta cards %[] etc-- --RETURNS VARCHAR(MAX) -- intended to be a function once developement finishedAS BEGIN DECLARE @Occurance INT , @CopyOfText VARCHAR(MAX) , @Result VARCHAR(MAX) , @EndPos INT -- Create Array of Start & End Positions DECLARE @Locations TABLE ( Occurance INT , StartPosition INT , EndPosition INT ) -- Populate Start Positions SET @CopyOfText = @Text SET @Occurance = 1 WHILE PATINDEX(@Pattern, @CopyOfText) > 0 BEGIN INSERT INTO @Locations ( Occurance , StartPosition ) VALUES ( @Occurance , PATINDEX(@Pattern, @CopyOfText) ) -- remove 1st char of pattern to prevent match again SELECT @CopyOfText = STUFF(@CopyOfText, PATINDEX(@Pattern, @CopyOfText), 1, CHAR(9)) SET @Occurance = @Occurance + 1 END -- Populate End Positions SET @CopyOfText = @Text SET @Occurance = @Occurance - 1 WHILE PATINDEX(REVERSE(@Pattern), REVERSE(@CopyOfText)) > 0 BEGIN SET @EndPos = DATALENGTH(@CopyOfText) - ( PATINDEX(REVERSE(@Pattern), REVERSE(@CopyOfText)) - 2 ) UPDATE @Locations SET EndPosition = @EndPos WHERE Occurance = @Occurance SET @Occurance = @Occurance - 1 -- remove 1st char of pattern to prevent match again SELECT @CopyOfText = STUFF(@CopyOfText, @EndPos - 1, 1,CHAR(9)) END SELECT * FROM @Locations -- Stuff replacement text between start & end markers SELECT @Text = STUFF(@Text, StartPosition, EndPosition - StartPosition, CHAR(9) + REPLICATE(CHAR(27), EndPosition - StartPosition- 1)) FROM @Locations SELECT @Text = REPLACE(@Text, CHAR(27), '') SELECT @Text = REPLACE(@Text, CHAR(9), @ReplaceWithText) RETURN @Text END[/code]Tue, 16 Feb 2010 10:59:06 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxIn fact, for the longer-strings, an abbreviated Boyer-Moore Search and Replace (or an unabbreviated one for really big strings) should be freaky fast.(edit: fixed spelling)Tue, 16 Feb 2010 10:40:05 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxHmm, this all seems like the kind of thing that a CLR UDF should burn through like C4.Tue, 16 Feb 2010 10:34:02 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxThanks Jeffappologies for the delay...When I get a moment I might try and build a function that fully supports the PATINDEX sytax.Tue, 16 Feb 2010 09:52:23 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspx[quote][b]C# Screw (2/9/2010)[/b][hr]Thanks Jeffyour PATINDEX idea seems a step closer to a dream 'WildReplace' function which might be :[code="sql"]CREATE FUNCTION dbo.fn_WildReplace(@SomeText varchar(max), @Pattern varchar(500), @ReplaceWith varchar(max)).....[/code] Edit: corrected syntaxUsage : [code="sql"]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. [/code]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?[/quote]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.Tue, 09 Feb 2010 05:51:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspx[quote][b]C# Screw (2/9/2010)[/b][hr]BTW I figured also if passing pattern to dream function then I would have to specify additional charaters to avoid clash with meta characters[code="sql"]'%[[b][[/b]]comment:#%[[b]][/b]]'[/code]where [[b][[/b]] and [[b]][/b]] represent single characters '[' and ']'[/quote]You don't need (or want) the []].... only the [[]. Look in Books Online under "Like".Tue, 09 Feb 2010 05:49:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxBTW I figured also if passing pattern to dream function then I would have to specify additional charaters to avoid clash with meta characters[code="sql"]'%[[b][[/b]]comment:#%[[b]][/b]]'[/code]where [[b][[/b]] and [[b]][/b]] represent single characters '[' and ']'Tue, 09 Feb 2010 02:53:48 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxThanks Jeffyour PATINDEX idea seems a step closer to a dream 'WildReplace' function which might be :[code="sql"]CREATE FUNCTION dbo.fn_WildReplace(@SomeText varchar(max), @Pattern varchar(500), @ReplaceWith varchar(max)).....[/code] Edit: corrected syntaxUsage : [code="sql"]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. [/code]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?Tue, 09 Feb 2010 02:31:49 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxWhen 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. :-DI believe the following may be what you're looking for.[code="sql"] CREATE FUNCTION dbo.PatternClean ( @pString VARCHAR(MAX), @pPatternLeft VARCHAR(MAX), @pPatternRight VARCHAR(MAX) )RETURNS VARCHAR(MAX) AS BEGINDECLARE @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 ENDGO[/code]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.[code="sql"]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 @LongStringPRINT ''PRINT dbo.PatternClean(@LongString,'[comment:#',']')[/code]Mon, 08 Feb 2010 23:17:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxThank you - I figured I was in Tag/end tag territory, rather like HTML markers.I had ago at creating a function :[code="sql"]--------------------------------- create some test dataCREATE 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-10CREATE 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 ENDGO--------------------------------- Test it SELECT dbo.fn_STUFF_Between_Tags(MyTextCol, '[COMMENT:#',']','')FROM #tTestDataGO--------------------------------- clean up DROP FUNCTION fn_STUFF_Between_TagsDROP TABLE #tTestData[/code]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]Mon, 08 Feb 2010 11:16:03 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxAgain without a Tally table (hence not the most efficient method), but here goes.[code="sql"]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 INTDECLARE @End IntSET @What = (SELECT MyTextCol FROM #tTestData)SET @End = 0WHILE @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)) CONTINUEEND SELECT @WhatReturns: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)[/code]Hope this get you started on a more efficient method, but it is the best I can do for now.Mon, 08 Feb 2010 10:45:28 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic861682-392-1.aspx[code="sql"]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[/code]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.'Mon, 08 Feb 2010 09:17:54 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspx[quote][b]bitbucket-25253 (2/8/2010)[/b][hr]Without using a Tally table[code="sql"]DECLARE @What VARCHAR(200)DECLARE @Start INTDECLARE @End IntSET @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,@EndSELECT SUBSTRING(@What,1,@Start-1) + SUBSTRING(@What,@Start+1,LEN(@What)-(@End +1))Result:Sometimes things may get [SQL GETDATE()] heated[/code]Is this what you are looking for?[/quote]Hi Ronkind 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.Mon, 08 Feb 2010 09:06:19 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxWithout using a Tally table[code="sql"]DECLARE @What VARCHAR(200)DECLARE @Start INTDECLARE @End IntSET @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,@EndSELECT SUBSTRING(@What,1,@Start-1) + SUBSTRING(@What,@Start+1,LEN(@What)-(@End +1))Result:Sometimes things may get [SQL GETDATE()] heated[/code]Is this what you are looking for?Mon, 08 Feb 2010 08:55:52 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxThere 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."Mon, 08 Feb 2010 08:39:26 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspx[quote][b]Lowell (2/8/2010)[/b][hr]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:[code]--===== 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 '')[/code][/quote]Hi Lowell I guess the start marker will be '[comment:#' end marker ']' if that helps.Mon, 08 Feb 2010 08:32:47 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspx[quote][b]RBarryYoung (2/8/2010)[/b][hr]Hmm, I apologize, now I am not sure that I have mentioned it to you before. Sorry ...[/quote]Thats OK Barry no worries - i did miss codes off the SQLMon, 08 Feb 2010 08:30:10 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxyou 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:[code]--===== 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 '')[/code]Mon, 08 Feb 2010 08:29:27 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxHi Lowellyes replace with same text - nothing in example:[code="sql"]UPDATE MyTable SET MyText = dbo.fn_WildReplace(MyText ,'[comment:#%]','')[/code]Mon, 08 Feb 2010 08:27:39 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxCan you explain a little more?if i had THIS as a comment:[code]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:)].'[/code]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:[code]--===== 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 '')[/code]Mon, 08 Feb 2010 08:23:13 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxHmm, I apologize, now I am not sure that I have mentioned it to you before. Sorry ...Mon, 08 Feb 2010 08:17:57 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxScrew: Is there some reason that you are [i]not[/i] using the code tags? I am pretty sure that I have mentioned this to you before.Mon, 08 Feb 2010 08:06:59 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic861682-392-1.aspxDear FriendsI 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:[code="sql"]UPDATE MyTable SET MyText = dbo.fn_WildReplace(MyText ,'[comment:#%]','')[/code][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.#ScrewMon, 08 Feb 2010 07:51:12 GMTC# Gnu