question

Kristen avatar image
Kristen asked

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-2000replaceregex
10 |1200 characters needed characters left characters exceeded

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

Madhivanan avatar image
Madhivanan answered

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 characters needed characters left characters exceeded

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

Kristen avatar image
Kristen answered

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 characters needed characters left characters exceeded

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

Madhivanan avatar image
Madhivanan answered

So, your expected result is "xxx yyy"?

10 |1200 characters needed characters left characters exceeded

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

mrdenny avatar image
mrdenny answered

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 characters needed characters left characters exceeded

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

TG avatar image
TG answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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