Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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' )
share|improve this question
up vote 12 down vote accepted

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%';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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