Kevin Foley Kevin Foley - 2 months ago 6
MySQL Question

Mysql Remove 0 and add 353 in contact numbers

i have alot of numbers with 10 digits like this

0987654321

I want a sql query that will remove the 0 and add 353.

i only want to do it for 10 digit numbers

Is there any body that can help me with this please?

Answer

Use a CASE expression to check the length.

Query

SELECT 
    `your_column_name`, 
    CASE WHEN LENGTH(`your_column_name`) = 10 
    AND LEFT(`your_column_name`, 1) = '0'
        THEN CONCAT('353', RIGHT(`your_column_name`, 9))
        ELSE `your_column_name` END as `new_column_name`
FROM `your_table_name`;

Or if you want to update that particular column. Then

Query

UPDATE `your_table_name`
SET `your_column_name` = CONCAT('353', RIGHT(`your_column_name`, 9))
WHERE LENGTH(`your_column_name`) = 10
AND LEFT(`your_column_name`, 1) = '0';
Comments