alter column on the fly

[hyster]

i created a db last week and i made a mistake. i forgot that excel deletes 0 in front of the number.

is there a query that will insert a 0 to a x amout of times?

 

IE: 759 to 000759, 4589 to 004589

 

the max number of chars is 6. some numbers have 3,4 or 5

 

this is a works problem and i cocked up not checking the excel file 1st

 

thanks

[suresh_kamrushi]

Before that you need to update structure of the table.

Change that field to varchar with

ALTER TABLE `tableName` CHANGE `FieldName` `FieldName` VARCHAR( 10 ) NOT NULL

 

Than you can append "0" to your values.

[suresh_kamrushi]

After updating your table structure, update your table as below:

 

update tableName set FieldName = LPAD(FieldName,6,'0');

 

It will work for you.

 

 

 

[hyster]

thx m8. sorted

[Pikachu2000]

IF they're supposed to be integers, don't change the data type to varchar. Set it as INT(6) UNSIGNED ZEROFILL.

[suresh_kamrushi]

Thanks Pikachu2000

 

Setting field as INT(6) UNSIGNED ZEROFILL is better to overcome the problem.

 

And it is new learning for me.

 

Thanks dude.