SQL Server: remove some data from a column

Question

I have a table with a description column. There are several html in this column.

This html then goes to the page and displays it. But sometimes there are comments, and sometimes they are not closed. I need to clear this data.

I found them through

 select * from table where description like '%<!--%'` 

I think this is not right, but I have no idea how to do it better.

Can you suggest a solution to my problem?

Edit
Here are some examples.

 <div class="text"> some data </div> <ul><!-- Some comment --> <li>test</li> <li>test2</li> <!-- Some comment </ul> 

Sincerely, Dmitri.

Answer 1

Try this option -

Query:

 DECLARE @temp TABLE ( id INT IDENTITY(1,1) , [description] NVARCHAR(MAX) ) INSERT INTO @temp ([description]) VALUES (' <div class="text"> some data <!--test</div> <ul> <li>test</li> <li>test2</li> <!-- Some comment </ul>') ;WITH cte AS ( SELECT t.id, t.token FROM ( SELECT t.id , token = SUBSTRING( t.[description] , number , ABS(CHARINDEX('<', t.[description], number + 1) - number)) FROM @temp t CROSS JOIN [master].dbo.spt_values n WHERE [type] = 'p' AND number <= LEN(t.[description]) - 1 AND SUBSTRING(t.[description], number, 1) = '<' ) t WHERE t.token NOT LIKE '<!--%' ) UPDATE t SET [description] = ( SELECT c.token FROM cte c WHERE c.id = t.id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') FROM @temp t SELECT * FROM @temp 

Results:

 <div class="text"> some data </div> <ul> <li>test</li> <li>test2</li> </ul> 

Answer 2

Use CASE WHEN to check for an open comment sequence and the inability to close the comment sequence and return a column that adds a close comment if necessary.

 SELECT * , CASE WHEN CHARINDEX('<!--', description) > 0 AND CHARINDEX('-->', description) = 0 THEN description + '-->' ELSE description END AS clean_description FROM dbo.[table] 

Alternatively, if you want to remove the broken comment, use

 SUBSTRING(description, 0, CHARINDEX('<!--', description)) 

for branching the THEN statement.