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
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015