hyster Posted January 17, 2011 Share Posted January 17, 2011 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 Quote Link to comment Share on other sites More sharing options...
suresh_kamrushi Posted January 17, 2011 Share Posted January 17, 2011 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. Quote Link to comment Share on other sites More sharing options...
suresh_kamrushi Posted January 17, 2011 Share Posted January 17, 2011 After updating your table structure, update your table as below: update tableName set FieldName = LPAD(FieldName,6,'0'); It will work for you. Quote Link to comment Share on other sites More sharing options...
hyster Posted January 17, 2011 Author Share Posted January 17, 2011 thx m8. sorted Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted January 17, 2011 Share Posted January 17, 2011 IF they're supposed to be integers, don't change the data type to varchar. Set it as INT(6) UNSIGNED ZEROFILL. Quote Link to comment Share on other sites More sharing options...
suresh_kamrushi Posted January 18, 2011 Share Posted January 18, 2011 Thanks Pikachu2000 Setting field as INT(6) UNSIGNED ZEROFILL is better to overcome the problem. And it is new learning for me. Thanks dude. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.