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?
EDIT 1:
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?
EDIT 2:
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' )
Best Answer
REPLACE
does not play with wildcards that way. I think you meant:You have no
WHERE
clause, so it tried to update 618 rows, but it did not find any instances of%TLD.com%
in that column. To see which rows should be affected, run aSELECT
instead: