MySQL Insert Problem

[minart]

Hi,

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?

Thanks

[debasisdas]

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

[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

[debasisdas]

What exactly is the problem ?

[minart]

What exactly is the problem ?

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

[debasisdas]

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

[minart]

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