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.