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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 11

Expert Comment

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

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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 Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

762 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