SQL replaces WHERE clause

Question

We have the following query that was written to replace anyone in the memo field with the HTML equivalent (&). When we wrote, we did not take into account that there are potentially other HTML tags in the field that also begin with "&" (ie, "", etc.). Since we must ensure that all ampersands are equivalent to HTML when used separately, and not in part of another tag, we must skip those that are part of another tag. However, the shortest HTML tag that can start with and seems to be 3 characters, and the longest - six characters, so _ _ _; to and _ _ _ _ _ _ _; in length ... are there any ideas on updating the where clause so that it does not update any data and that are executed using ";" in the next 4-7 characters after &? Thank.

 UPDATE STOCKMEM
 SET INETFDESC = CAST(
                       REPLACE(
                               REPLACE(
                                       CAST(INETFDESC as NVarchar(MAX))
                               ,'&','&')
                       , '&', ,'&')AS NText)
  WHERE INETFDESC LIKE '%&[^amp;]%' 

Answer 1

This is probably not the best way to deal with this problem, but ...

You can use the underscore character _as an indicator that there should be some kind of character in this place, which actually makes it a character counter in such a situation. Just a quick example:

SELECT REPLACE('This is &[^amp;] just a test.','&[^amp;]','&')
WHERE 'This is &[^amp;] just a test.' LIKE '%&___;%'

This will not return a value because the line in the sentence WHEREdoes not include &, followed by three characters _ _ _, and then a semicolon.

SELECT REPLACE('This is &[^amp;] just a test.','&[^amp;]','&')
WHERE 'This is &[^amp;] just a test.' LIKE '%&_____;%' 

This will return the value because the condition LIKEis encountered using a line in a sentence WHERE: &_ _ _ _ _;(distance added for clarity)

Maybe you could use this to your advantage?

Answer 2

, , . , , . , , , , .

set nocount on
--drop table #HtmlTest
select CONVERT( nvarchar(255) , 
  N'The & & z; HTML & replacement < > é ε test & a; ' )   as test
  into #HtmlTest

select test from #HtmlTest

declare @posStart int, @posStart1 int, @posStart2 int, @posEnd int, @isEntity bit
set @posStart = 1 

while (@posStart != 0)
  begin
    select @posStart1 = charindex('&', test, @posStart + 1)  from #HtmlTest
    select @posStart2 
        = patindex('%&[a-z]%;%', substring(test, @posStart + 1, 99999)) 
        + @posStart    from #HtmlTest
    set    @isEntity  = IIF(@posStart1 = @posStart2, 1, 0)
    select @posEnd    = charindex(';', test, @posStart1 + 1) from #HtmlTest

    set @posStart = @posStart1

    if (@isEntity = 0 and @posStart1 > 0) 
      begin
        update #HtmlTest 
          set test = SUBSTRING(test, 1, @posStart1 - 1) + '&' 
                   + SUBSTRING(test, @posStart1 + 1, 999999) 
        select test from #HtmlTest
        set @posStart += 4
      end
  end
select test from #HtmlTest
set nocount off

Answer 3

I think this will work:

 UPDATE STOCKMEM
 SET INETFDESC = CAST(
                 REPLACE(
                     CAST(INETFDESC as NVarchar(MAX)), '& ', '&amp ')
                 ) AS NText
              )

If it &is part of any tag, there will be no space left behind it, so replace each with the &next space with &, followed by a space.