MYSQL column update

Question

In mysql update the number in the column that it would be 9 digits

for example:

658 were recorded, but must be 000000658, 
if 96258, this must be 000096258, front contributed to 0.

Thanks

That's rendering. Save data in the db, leave rendering to the UI — Bohemian, Nov 21 '13 at 09:05
duplicate: http://stackoverflow.com/questions/5191494/padding-the-beginning-of-a-mysql-int-field-with-zeroes ? — Joachim Rohde, Nov 21 '13 at 09:05

score 2 · Answer 1 · answered Nov 21 '13 at 09:12

2

I think you'll have to change the datatype from int to varchar(9). Just have your code add on the zero's before committing to your database.
Example in PHP:

$foo = "604";
while(strlen($foo) < 9)
{
    $foo = "0".$foo;
}

$foo now equals 000000604. All you would need to do is write a loop to pull from your database and commit back to it.

answered Nov 21 '13 at 09:12

DivingMaster

59
3

i think the tag is with `mysql` – vhadalgi Nov 21 '13 at 09:18

Ali · Accepted Answer · 2013-11-21T09:26:04.647

1

While creating column use this:

ALTER table `table` CHANGE `column` `column` VARCHAR(9);

While inserting or updating data use this:

UPDATE table SET `column` = LPAD(`column`, 9, '0'); #pads everything

edited Nov 21 '13 at 09:26

answered Nov 21 '13 at 09:19

Ali

5,021
4
26
45

MYSQL column update

2 Answers2