Best way to replace a "like" substring?

Question

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.

Answer 1

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

Answer 2

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

Answer 3

0

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.

answered Oct 08 '09 at 02:55 PM

mrdenny
897 ● 3

Yup, I agree. This is just a work-around. We can deploy single SProc fixes easily (Do the QA, run it up the flagpole!, deploy). Deploying new ASP requires a week's worth of automated QA runs, building deployment packages, and the whole nine yards - so nothing beats the next 6-monthly-release for core code, so all critical bug fixes are done by SProc change wherever possible. We have used RegEx from SQL using COM objects, but I'm reluctant to do that unless no other solution - we get periodic SQL crashes talking to COM objects.

Oct 09 '09 at 06:24 AM Kristen ♦

Something like a RegEx dll shouldn't be to bad. If you are getting issues with COM you may have objects that are created by the COM whihc aren't disposed correctly. They aren't cleaned up automatically as they are created in SQLs memory space, not here own and SQL doesn't garbage collect for the COM objects.

Oct 09 '09 at 02:57 PM mrdenny

(comments are locked)

Answer 4

0

So, your expected result is "xxx yyy"?

answered Oct 08 '09 at 09:15 AM

Madhivanan
1.1k ● 1 ● 2 ● 6

Errrm... no! "AaaABbbBDdD" is what I need.

Oct 09 '09 at 06:20 AM Kristen ♦

(comments are locked)

Answer 5

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

Best way to replace a "like" substring?

Follow this question

Related Questions