leading zeros in mysql

Question

I have a form where user updates the table in database with serial numbers, the problem is that in my .csv file serial number has value 0 and after inserting it, it has 000000, same for the 1, after inserting it is 000001. I need it in exact way like it is in .csv file. My code for the LOAD is:

LOAD DATA LOCAL INFILE path_to_file.csv
INTO TABLE im_seriennummer CHARACTER SET latin1
        FIELDS TERMINATED BY ";"
        IGNORE 1 LINES
        (sn,description_sn)

In .csv file it is like this:

0

1

And in database

000000

000001

In the database sn is varchar(16). Is this problem familiar to anyone? Please don't tell me to change the type of field, I need to have it in varchar since some serial numbers are like this MT 002

also provide first few line from CSV if possible, do not upload complete file for security reason. — Sumit Gupta, Feb 18 '14 at 13:33

score 0 · Answer 1 · answered Feb 18 '14 at 13:37

The solution,i think, is to use a temp table from import the csv.

CREATE TEMPORARY TABLE tmptab LIKE im_seriennummer;

LOAD DATA LOCAL INFILE path_to_file.csv
INTO TABLE tmptab CHARACTER SET latin1
        FIELDS TERMINATED BY ";"
        IGNORE 1 LINES
        (sn,description_sn)


UPDATE tmptab SET SERIAL = RIGHT(CONCAT('000000', SERIAL), 6)

INSERT INTO im_seriennummer
SELECT * FROM tmptab

DROP TEMPORARY TABLE tmptab;

leading zeros in mysql

1 Answers1