Member Avatar for 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

  • 2 Contributors
  • 6 Replies
  • 158 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by minart
Member Avatar for debasisdas

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

Member Avatar for 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

Member Avatar for debasisdas

What exactly is the problem ?

Member Avatar for 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.

Member Avatar for 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

Edited by debasisdas because: n/a
Member Avatar for minart

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.