SQL Select Query Returns 5,448 rows, the update query affects 93,205 rows, but nothing is actually updated?

Question

If I run this:

SELECT *
FROM [myDB].[dbo].[content]
where content_html like '%<images>%<img src=''/'' alt=''''>%</img>%</images>%'

It returns 5,448 rows.

Those empty image tags are breaking my pulls in Ektron – our current CMS. They suggested I replace those empty image tags with

So I did this Update query:

UPDATE [myDB].[dbo].[content]
SET content_html=REPLACE(CONVERT(VARCHAR(MAX), content_html),'%<images>%<img src=''/'' alt=''''>%</img>%</images>%','<images></images>')

It returns this: (93205 row(s) affected)

But nothing actually gets changed. I tried it with one specific record and it says it was affected but the data remains the same.

Answer 1

First, your where clause from the first query is missing from the second:

Second, unless you want to replace the entire field, you don't want leading and trailing wild card symbols in your replace statement. I don't know if you can even use wildcard statements in a replace command - it's not used as an example in any Microsoft documentation that I've read and a few google searches didn't yield anything:

UPDATE [myDB].[dbo].[content]
SET content_html=REPLACE(CONVERT(VARCHAR(MAX), content_html),'<images>%<img src=''/'' alt=''''>%</img>%</images>','<images></images>')
where content_html like '%<images>%<img src=''/'' alt=''''>%</img>%</images>%'

EDIT:

A REALLY easy way to test this would be:

SELECT content_html as [Original], REPLACE(CONVERT(VARCHAR(MAX), content_html),'%<images>%<img src=''/'' alt=''''>%</img>%</images>%','<images></images>') as [Replaced]
FROM YourTable
WHERE content_html like '%<images>%<img src=''/'' alt=''''>%</img>%</images>%'

This query will give you the original value - and the replaced value that you're creating - and only on records that match your like expression. If the two columns are different, it works. If they're the same, it doesn't and you'll need to use other code to accomplish the desired results.

FINAL EDIT:

I tested this an REPLACE only works on literal strings. Your wildcards will not work in your replace function and you will need either very complex SQL - or application code to achieve your goal. The app route is exponentially better, so do that! Best of luck!

Answer 2

Your update statement doesn't have a WHERE clause. That means it will operate on every row. Even if the data in content_html does not change, the UPDATE statement will rewrite it again back to the table. Why? Because that's what you told it to do.

You will need to use this to only affect the 5448 rows you want:

UPDATE [myDB].[dbo].[content]
SET content_html=REPLACE(CONVERT(VARCHAR(MAX), content_html),'%<images>%<img src=''/'' alt=''''>%</img>%</images>%','<images></images>')
where content_html like '%<images>%<img src=''/'' alt=''''>%</img>%</images>%'

However, you're still unlikely to do anything because the REPLACE() function is a string literal replace. It doesn't know anything about the wildcards that LIKE uses. So the query is pattern matching to find the rows to update, but it won't every find the literal string %<images>%<img src='/' alt=''>%</img>%</images>% in content_html, which the REPLACE() function probably never finds.

There is no built in regex replace function in SQL Server. There are other options, but I don't think any of them are particularly useful for you. You might be able to use a series of PATINDEX() calls to find <images> and <img src='/' alt=''> and </images>, use some comparison to decide if the <img tag is inside those, and then use STUFF(), but without knowing your data I've no way of knowing how well that would work.

The easiest way to use a real regex replace is probably to SELECT the fields to a DataTable, apply your regex from C# or PowerShell, and then merge the changes back.