x

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.

more ▼

asked Oct 08, 2009 at 08:22 AM in Default

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left

7 answers: sort newest

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
more ▼

answered Oct 08, 2009 at 10:50 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

@Madhivanan you should create a single answer then update it as needed or add comments to it. Multiple answers aren't needed and clutter the system.
Oct 08, 2009 at 02:54 PM mrdenny
Works fine for all my test cases, thanks!
Oct 09, 2009 at 06:41 AM Kristen ♦
Only possible issue might be that it inserts "~" which might take me over the 8,000 character limit.
Oct 09, 2009 at 06:49 AM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 08, 2009 at 03:52 PM

TG gravatar image

TG
1.8k 1 3

@TG, SQL 2000...
Oct 08, 2009 at 10:29 PM mrdenny
Interesting approach. I did wonder whether I could join my string to my Tally Table, but the maths of working out what nested with what, and if I used STUFF to remove a bit how that would effect the parameters for the remaining STUFFs that followed. My brain hurt at that point so I wrote a LOOP (but I did consider posting the question anonymously!!)
Oct 09, 2009 at 06:27 AM Kristen ♦

Ah yep, Thanks mrdenny, I missed the 2000 tag :(
I'll get the hang of this site sooner or later...

I was actually working on a different approach prior to this one that would have eventually resolved down to a nested STUFF string which would then need to be exec'd. I think that one was all 2000 code but it was rather convoluted. If I get too bored today I may take another shot :)
Oct 09, 2009 at 12:25 PM TG
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 08, 2009 at 02:55 PM

mrdenny gravatar image

mrdenny
908 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, 2009 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, 2009 at 02:57 PM mrdenny
(comments are locked)
10|1200 characters needed characters left

So, your expected result is "xxx yyy"?

more ▼

answered Oct 08, 2009 at 09:15 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

Errrm... no! "AaaABbbBDdD" is what I need.
Oct 09, 2009 at 06:20 AM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

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 &lt;= 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
more ▼

answered Oct 08, 2009 at 09:15 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x473
x18
x7

asked: Oct 08, 2009 at 08:22 AM

Seen: 5095 times

Last Updated: Oct 08, 2009 at 08:22 AM