CODEWITHSUNDEEP
mysqlsql
Trevor Ackermann
Trevor Ackermann

Reputation: 176

Insert Numerical 0 in front of current value in Mysql DB column

I have a a MySQL database the information I uploaded to populate the database is from a CSV file. The CSV file has a numerical value eg 9875637892. As I have over 2000 rows in the CSV the leading 0 is missing. I cannot for obvious reasons go thru 2000 plus records to add leading zero I need to have 09875637892 in each row.

Is there a way I can run a SQL string to add the 0 in front of 9875637892 from SQL?

Upvotes: 0

Views: 91

Answers (2)

xerx593
xerx593

Reputation: 13261

L(-eft)PAD seems an apropriate solution:

-- simple test:
SELECT LPAD('9875637892', 11, '0');

===========================
Number of Records: 1
===========================
LPAD('9875637892', 11, '0')
---------------------------
09875637892

-- update table:
UPDATE yourtable
SET yourcolumn = LPAD(yourcolumn, 11, '0');

(assuming 11 digits max/to fill up)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Numeric values do not have leading zeros. If the value in the CSV file does, then change the datatype to a string and re-import the data.

If that is not possible, then make the change in place:

alter table t modify col varchar(255);

update t
    set col = concat('0', col);

If the value is already a string, you can just run the update.

Upvotes: 2

Related Questions