mysql, replace single single characters.

Posted on 2010-01-11
Medium Priority
Last Modified: 2012-05-08
Okay, so I have to replace a lot of fields in my database. For additional explanation please see this question -- http://www.experts-exchange.com/Database/Miscellaneous/Q_25019604.html --

I'm going through and for each user I am having to choose a single 'user_id' value, delete the duplicate 'user_id' and then I had been running a replace query on my 'contacts' table to change all contacts to the new 'user_id'.  My problem comes when I am trying to run the replace query on 'user_id' 's that are not 3 digits. So when running this query

UPDATE contacts SET user_id = replace( user_id, '65', '64' )

Any 'user_id' containing 65 (165, 265, 365...) will be changed to '64'

How can I properly configure my script to change these values correctly?
Question by:jshiffty
LVL 11

Expert Comment

ID: 26290423
update contacts SET user_id=64 where user_id like '%65%'
LVL 61

Accepted Solution

HainKurt earned 2000 total points
ID: 26290444
update contacts
SET user_id=replace(userid, '65','64')
where user_id like '%65%'


mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

Author Closing Comment

ID: 31675943
Awesome, thanks again Hain. Was a bit nervous after the first run had 5000 rows affected! But of course after taking a look I saw query worked flawlessly and properly filtered and changed correct fields.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question