Adding a leading zero to some values in column in MySQL

Question

I have a CSV file sent to me in CSV  The field of interest is 8 digits  Some of those started with a 0  The field was sent numeric  So  I now have dropped some leading zeros   I already converted the field to varchar  I now need to do this   I have this now   12345678 1234567   I need to have this   12345678 01234567

User · Answer

I had similar problem when importing phone number data from excel to mysql database. So a simple trick without the need to identify the length of the phone number (because the length of the phone numbers varied in my data):

UPDATE table SET phone_num = concat('0', phone_num)

I just concated 0 in front of the phone_num.

User · Answer

Possibly   select lpad column  8  0  from table      Edited in response to question from mylesg  in comments below      ok  seems to make the change on the query- but how do I make it stick  change it  permanently in the table  I tried an UPDATE instead of SELECT   I m assuming that you used a query similar to   UPDATE table SET columnName lpad nums 8 0     If that was successful  but the table s values are still without leading-zeroes  then I d suggest you probably set the column as a numeric type  If that s the case then you d need to alter the table so that the column is of a text varchar   type in order to preserve the leading zeroes   First   ALTER TABLE  table  CHANGE  numberColumn   numberColumn  CHAR 8     Second  run the update   UPDATE table SET  numberColumn  LPAD  numberColum   8   0      This should  then  preserve the leading-zeroes  the down-side is that the column is no longer strictly of a numeric type  so you may have to enforce more strict validation  depending on your use-case  to ensure that non-numerals aren t entered into that column   References    lpad

User · Answer

A previous answer using LPAD   is optimal   However  in the event you want to do special or advanced processing  here is a method that allows more iterative control over the padding   Also serves as an example using other constructs to achieve the same thing  UPDATE     mytable SET     mycolumn   CONCAT          REPEAT               quot 0 quot               8 - LENGTH mycolumn                     mycolumn       WHERE     LENGTH mycolumn   lt  8

User · Answer

Change the field back to numeric and use ZEROFILL to keep the zeros  or  use LPAD    SELECT LPAD  1234567   8   0

[mysql] Adding a leading zero to some values in column in MySQL

Examples related to mysql

Examples related to sql

Examples related to database