CODEWITHSUNDEEP
mysql
superfly
superfly

Reputation: 3

How to add zero to mysql (varchar) field, but only to ones that start with different than zero number. Lenght of phone numbers vary

I have imported a few thousand records in my existing CRM database from CSV and apparently all leading zeroes in the phone numbers column have disappeared. In searching for an answer all other questions I could find were about fixed length of the string. but my phone numbers vary in length. The ones that need their leading zeroes back are the ones starting with different than zero number. The old records (prior to my CSV import) are fine and I do not need another zero to go in front of them. Please bare in mind I am not a programmer and this is all very difficult to me - even to explain.

I've tried to export the database table via phpmyadmin in CSV and sort the problem out in LibreOffice and then put it back (import) ...but there are records NULL that are not in quotes "" when exported and when I save the file in LibreOffice all fields are in quotes including the NULL ones. So I guess this will not work. It is a production environment. The table in question is the "contacts" table for a SugarCRM CE database. The field is varchar(100). So to summarize my question is: is there a function that I can put in phpmyadmin or in the terminal (i have root access), that will identify the records in this particular table (contacts), that start with number different than zero and ad one zero in front? I know that I am expected to show a code that I've tried and to give some feedback what the result is, but I just can't. Sorry. Any help will be highly appreciated.

mysql server fields

Upvotes: 0

Views: 560

Answers (1)

Nedret Recep
Nedret Recep

Reputation: 728

UPDATE table1 SET phone = CONCAT("0", phone) WHERE LEFT(phone, 1) <> "0";

You can first run SELECT with the same condition to see if it correctly return the rows that have phone numbers that lost their leading zeros

Upvotes: 1

Related Questions