Hoping someone can help with a query I'm stuck on.

I've got a field (sm_contact1number) containing phone numbers. Some of the data has been imported via Excel and its stripped some of the 0's out of the start of telephone numbers, so 0121 3324234 now looks like 121 3324234.. There are also mobile numbers with the same problem.

Can anyone help me out with how I can update:

Numbers starting with a 1 have a 0 added at the front
Numbers strting with a 7 have a 0 added at the front

.. for that field. Also, I'm pretty sure I can narrow down this problem to a specific number of records (eg 100-441) - is there any way I can add that into the query?


6 Years
Discussion Span
Last Post by minart

Check for the 1st character of the string ,if that start with 1 0r 7 concatenate with 0.

Hi... Thanks, yes... that's the bit I'm having the problem with :P


What exactly is the problem ?

I dont know how to write the SQL UPDATE code to search for those 2 numbers (separately) and concat.


Better to do that in a procedure.

try this sample code

UPDATE table1 SET field1  = '0'||field1 
WHERE (field1 LIKE '1%' OR field1 LIKE '7%')

NOTE:-The above code is in oracle syntax, you have to change to mysql

Edited by debasisdas: n/a


Thanks for the reply.
That code seems to just replace the offending entries with a '1'...

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.