How to update value after modifying the current value in MySQL?-CodePudding

I have more than 5000 entries having incorrect values. In users table, numbers column has below value

225 2250500000000

I want to remove 225 and keep and rest number. So the numbers column should have below

 2250500000000

How can I do that with MySQL query?

CodePudding user response：

try this:

UPDATE yourTable
set yourField = REPLACE(yourField,'225 ',' ')
WHERE yourField LIKE '225 %';

CodePudding user response：

In case your data are not consistent like:

 -------------------- 
|      numbers       |
 -------------------- 
| 225 2250500000000  |
| 2250 2250500000000 |
| 22 2250500000000   |
|  2250500000000     |
 --------------------

Then I suggest SUBSTRING_INDEX() to get the numbers at the end of and CONCAT() to add the back:

SELECT numbers,
       CONCAT(' ',SUBSTRING_INDEX(numbers,' ',-1))
 FROM test;

 -------------------- ---------------------------------------------- 
|      numbers       |  CONCAT(' ',SUBSTRING_INDEX(numbers,' ',-1)) |
 -------------------- ---------------------------------------------- 
| 225 2250500000000  |    2250500000000                             |
| 2250 2250500000000 |    2250500000000                             |
| 22 2250500000000   |    2250500000000                             |
|  2250500000000     |    2250500000000                             |
 -------------------- ----------------------------------------------

with UPDATE, maybe add a WHERE if necessary:

UPDATE test 
   SET numbers=CONCAT(' ',SUBSTRING_INDEX(numbers,' ',-1))
WHERE numbers LIKE '% %';

Here's another method; use LOCATE to find the location of ' ', then use SUBSTRING() from that position:

SELECT numbers,
       SUBSTRING(numbers, LOCATE(' ',numbers))
 FROM test

CodePudding user response：

I have tried and done successfully, below is the query

UPDATE `users`
SET `numbers` = SUBSTR(numbers, 4, 18)