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

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)

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

 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

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:#',']')

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

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. 

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