I'm seeking a certain string in a field and want to replace it with a new string. Specifically, I want all references to one URL to be changed to another URL. I've crafted this SQL statement and am running it at a
mysql> prompt on CentOS 5.5 using MySQL Community Server 5.1.54.
update [table] set [field] = REPLACE([field],'%domain.com%','%domain.org%');
The response is:
Query OK, 0 rows affected (0.02 sec) Rows matched: 618 Changed: 0 Warnings: 0
How can I track down why no changes are being made?
Thanks to Aaron Bertrand, I discovered that
REPLACE() cannot handle wildcards and I was using it completely wrong (think: missing
WHERE clause). Here's is my reformed statement:
UPDATE [table] SET [column] = REPLACE ( [column], 'companydomain.com', 'companydomain.org' ) WHERE [column] LIKE '%companydomain.com%';
To which I receive the old, familiar:
Query OK, 0 rows affected (0.02 sec) Rows matched: 167 Changed: 0 Warnings: 0
What could I be doing wrong?
I'll tell you what I was doing wrong!! I was not questioning assumptions. My assumption was that the string that I was replacing was in all lower case. The WHERE clause was returning all things that looked
LIKE %companydomain.com%. That includes all capitalization permutations such as CompanyDomain.com, CoMpAnYdOmAiN.com and etc.
It passed that on to
REPLACE() which then was looking strictly for companydomain.com to then replace it with companydomain.org.
REPLACE ( [column], 'companydomain.com', 'companydomain.org' )
So of course my records were being returned, but nothing was being replaced. Once I changed
REPLACE() to take the capitalization into account, all records were updated and it appears that all is well. The correct
REPLACE() syntax for my scenario was thus:
REPLACE ( [column], 'CompanyDomain.com', 'companydomain.org' )