9

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' )
12

REPLACE does not play with wildcards that way. I think you meant:

UPDATE [table] 
  SET [column] = REPLACE([column],'TLD.com','TLD.org')
  WHERE [column] LIKE '%TLD.com%';

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 a SELECT instead:

SELECT [column], REPLACE([column], 'TLD.com', 'TLD.org') AS new_value
  FROM [table]
  WHERE [column] LIKE '%TLD.com%';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.