UPDATE MyTable 
SET MyText = dbo.fn_WildReplace(MyText ,'[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:)].'

--===== 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 
                            '')

UPDATE MyTable 
SET MyText = dbo.fn_WildReplace(MyText ,'[comment:#%]','')

--===== 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 
                            '')

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