Take the 2-minute tour ×
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.

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
    
Are you SURE you're really running that exact query? –  ErikE Feb 8 '12 at 5:18
add comment

1 Answer

up vote 9 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
    
Aha, I didn't know that replace couldn't handle wildcards. That gives me something to go on. I'm not sure your statement would work for my exact scenario because I have larger urls like TLD.com/blah/foo.bar that need to be changed to TLD.org/blah/foo.bar. To my untrained SQL eye, it appears that the replace function in your statement will only find strict TLD.com string and replace them with strict TLD.org strings. What I might end up doing is dumping the database and then crunching the text with vim before pulling it back into MySQL. How crazy is that? –  Wesley Feb 8 '12 at 4:21
    
No, that's not what happens. REPLACE() only replaces the portion of the string. I'll update my example to demonstrate. Of course depending on your charset the replace may need to handle all case varieties (tld.com, TLD.com, TLD.COM, TLd.com, etc...) or apply lower-case first. –  Aaron Bertrand Feb 8 '12 at 4:23
    
Thanks! I looked more into it and think I understand a bit better. However, after researching, thinking I understood and then performing a new statement... it appears that I am not understanding like I thought I was. >_< My question has been updated. –  Wesley Feb 8 '12 at 5:08
    
Well what happens when you run the SELECT query I posted? Could you show a few rows of output from that so we have some clue as to why 0 rows are getting updated? –  Aaron Bertrand Feb 8 '12 at 5:10
    
The select query returns 167 rows. It's rather ugly output - HTML. This is the post_content field in the wp_posts table of a Wordpress installation. –  Wesley Feb 8 '12 at 5:13
show 6 more comments

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.