CODEWITHSUNDEEP
mysql
nsilva
nsilva

Reputation: 5614

SQL - Strip first digit if it starts with the number '9'

Basically I have a table called 'Telephone' with over 100,000+ records.

In this database I have numbers such as:-

90123456789

91234567893

97126372319

Basically I want to Update the Telephone.DB and SET the numbers so it strips the first number only if it starts with the number '9', so the result would end up as follows:-

0123456789

1234567893

7126372319

Any idea how I can achieve this using MySQL?

Upvotes: 0

Views: 457

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

Provided that the datatype is a string type, this should do it;

UPDATE numbers 
SET number = SUBSTR(number, 2)
WHERE number LIKE '9%';

An SQLfiddle to test with.

As always, test yourself before running updates from random people on the Internet on your production database :)

Upvotes: 4

Wallack
Wallack

Reputation: 792

Ok, this works for a VARCHAR column. You could always convert on the fly the value to varchar:

SELECT CONCAT(REPLACE(SUBSTR(PHONEFIELD,1,1),9,''), SUBSTR(PHONEFIELD,2)) AS PHONEWITHOUTNINE FROM TABLENAME

Check it out and tell me if it works.

Upvotes: 1

Related Questions