How do I set 0 for total length except the number exist in row in MySql

Question

The filed length is varchar(7) I just want to set 0 before the ID number, for all rows as below:

TraineeID       TraineeID
   74            0000074
 1290            0001290
51315            0051315

Please help.

Regards Mir

Use can use the format() function in mysql scripts and keep data saved as int. Not sure please describe better — Drew, Jun 17 '15 at 18:51
If you don't want to alter the data within the database, then you could look into padding the zeros in the programming language using the data. For example in php: `sprintf("%07d", $row['TraineeID']);` — Siphon, Jun 17 '15 at 18:54
@DrewPierce, I need to print it as same format or search with 7 character, Is there still way to keep it as int? — Mir Abzal Ali, Jun 17 '15 at 19:07

score 2 · Accepted Answer · answered Jun 17 '15 at 18:50

2

SELECT LPAD(TraineeID,7,'0');

answered Jun 17 '15 at 18:50

Giorgos Betsos

I need to update all existing number in `tbl_assigned` in column 'TraineeID'. could you tell please where I put the table name. – Mir Abzal Ali Jun 17 '15 at 19:06
1

Mir these are basic questions can u go thru a basic tutorial online please – Drew Jun 17 '15 at 19:13
@MirAbzalAli You should check the [manual](https://dev.mysql.com/doc/refman/5.0/en/update.html) – Giorgos Betsos Jun 17 '15 at 19:16

1 Answers1