Best way to replace a "like" substring?

Question

question

Kristen asked Oct 8, '09

Best way to replace a "like" substring?

I have a column which contains HTML templates. These can contain "internal" comments which are entered as:

                    
{/*}My Comment{*/}

these are stripped out by the front end before the rendered HTML is sent to the user's browser.

However, I have hit a snag where I want to strip these out in a situation where the front end doesn't get the chance to remove them. For now my quickest way will be in SQL (in the Sproc that retrieves the templates)

So given a

                    
DECLARE @MyString varchar(8000)                    
SET @MyString = 'xxx{/*}My Comment{*/}yyy'

what's the best way to remove the comment?

This is for SQL2000, iterative loops are OK, the comments may be nested.

sql-server-2000 replace regex

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-08T10:50:44Z

Madhivanan answered Oct 8, '09

Here is my attempt without loop But not sure if this works for all set of data

It is to TEST to test it

DECLARE @string VARCHAR(8000)  ,@data varchar(8000)            
            
SELECT @string = 'AaaA{/*}XxxX{/*}YyyyY{*/}ZzzzZ{*/}BbbB{/*}CCC{*/}DdD'            
select @string=replace(REPLACE(@string, '{/*}','~{/*}'),'{*/}','~{*/}')            
            
declare @t table(id int identity(1,1),data varchar(8000))            
insert into @t(data)            
SELECT             
    SUBSTRING(@string, n, CHARINDEX('~', @string + '~', n ) - n)            
FROM             
    (             
        SELECT number as n FROM master..spt_values            
    	where type='p'             
    ) numbers             
WHERE             
    SUBSTRING( '~' + @string, n, 1 ) = '~'            
 set @string=''            
            
            
 set @string=''            
 select @string =@string+data from            
 (            
 select data from @t            
 where id=1            
 union all            
 select substring(data,5,len(data))  from @t            
 where (data  like '{*/}%' and id%2=1)            
 ) as t            
            
 select @string

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-08T09:15:09Z

Kristen answered Oct 8, '09

OK, here's my first shot, all improvements welcome!

            
DECLARE @strTest varchar(8000),            
    	   @intStart int,            
    	   @intEnd int            
            
SELECT @strTest = 'AaaA{/*}XxxX{/*}YyyyY{*/}ZzzzZ{*/}BbbB{/*}CCC{*/}DdD'            
            
WHILE 1=1            
BEGIN            
            
    SELECT	@intEnd = PATINDEX('%{*/}%', @strTest) + 3	-- First close position            
            
    IF @intEnd <= 3            
    BEGIN            
    	GOTO LoopExit            
    END            
            
    SELECT	@intStart = PATINDEX('%}*/{%',             
                                 REVERSE(SUBSTRING(@strTest, 1, @intEnd-4)))            
            
    IF @intStart = 0            
    BEGIN            
    	-- Got a closing marker, but no opening marker            
    	GOTO LoopExit            
    END            
            
    SELECT	@intStart = @intEnd - 4 - 4 + 2 - @intStart            
            
    SELECT	@strTest = STUFF(@strTest, @intStart, @intEnd-@intStart+1, '')            
END            
            
LoopExit:            
            
SELECT  [Output] = @strTest

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-10-08T09:15:30Z

Madhivanan answered Oct 8, '09

So, your expected result is "xxx yyy"?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2009-10-08T14:55:25Z

mrdenny answered Oct 8, '09

Honestly your best bet will be to handle this in the front end. It that simply isn't possible, then create an extended stored procedure and use a regular expression to handle this. It will be much faster to run the data through a regex than to do this in T/SQL.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 5 · 2009-10-08T15:52:17Z

TG answered Oct 8, '09

If you need to do this from sql code then I believe the most efficient way will be by a CLR function. However, I couldn't resist trying to jam a square peg into a round hole so here is my version of a non-looping sql solution. I haven't performance tested it but I suspect your "first shot" Kristen will out perform this:

declare @strTest varchar(8000)            
set @strTest = 'AaaA{/*}XxxX{/*}YyyyY{*/}ZzzzZ{*/}BbbB{/*}CCC{*/}DdD'            
            
;with cte (n, c, lev)            
as (--break the string into a table - one row per character with a comment nesting level indicator            
       select number            
              ,substring(@strTest, number, 1)            
              ,case             
                 when substring(s.s, number, 4) = '{/*}' then 1            
                 when substring(s.s, number-4, 4) = '{*/}' then -1            
                 else 0            
                 end             
       from   (select @strTest s) s            
       --your favorite numbers table or function            
       join master..spt_values n             
              on n.type = 'P'            
              and n.number <= len(s.s)            
              and n.number > 0            
       )            
select [output]            
from   (--re-assemble the string without the comments            
       select c + ''            
       from   cte i            
       cross apply (select sum(lev) lev from cte where n <= i.n) ca            
       where  ca.lev = 0            
       order by i.n            
       for xml path('')             
       ) as t ([output])            
            
            
output            
--------------            
AaaABbbBDdD

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question