SQL Update field remove characters-CodePudding

I used this SQL statement to update a table ... and now I need to revert the change. The statement below added 000 to the end of the u_id now i need to remove the 000 that was added

UPDATE downloadable_link
SET    u_id = CONCAT(u_id, '000')
WHERE  product_sku = 'test-add'

CodePudding user response：

I'd do it this way:

UPDATE downloadable_link
SET    u_id = TRIM(TRAILING '000' FROM u_id)
WHERE  product_sku = 'test-add';

This is safer because it removes '000' only from the end. Just in case there are some u_id values with '000' in the middle.

See https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_trim for details on the TRIM() function.

If you want to test how the solution will handle different values, then test some queries by running them in the MySQL client:

mysql> select trim(trailing '000' from 'a000bc000') as result;
 -------- 
| result |
 -------- 
| a000bc |
 --------