Like mysql random null leading string field

Question

I am not sure if you understood my question correctly. Therefore, I can explain a little. I have a customer table needed for testing with a different number (whether or not it is). So this is the script:

customer_db

+----+------------+ | ID | NUMBER | +----+------------+ | 1 | 0812345678 | +----+------------+ | 2 | 0812345678 | +----+------------+ | 3 | 0812345678 | +----+------------+ . . . |100 | 0812345678 | 

According to the table. I ran this script:

 UPDATE customer_db SET number = FLOOR(0812345678 + rand()*1000000); 

Doing this. I expect the field to remain in the same format with the leading “081” and a random remaining 6 digits. But it is not . The table will be as follows:

 +----+------------+ | ID | NUMBER | +----+------------+ | 1 | 812246797 | +----+------------+ | 2 | 816548798 | +----+------------+ | 3 | 815787898 | +----+------------+ . . . |100 | 813454687 | 

This is 9 digits instead of 10. Because there is no "0". What to do to remain the leading "0" after random.

Answer 1

Like @ B-and-P, he describes in his comment. You can do this using LPAD .

 UPDATE customer_db SET number = LPAD(FLOOR(number + rand()*1000000),10,0) 

LPAD uses 3 parameters; a string, the total number of characters and the last, but no less than which character should be used to fill.

Answer 2

As an alternative to @Benz's answer, you can try this (works on my end):

 INSERT INTO Table1 (`NUMBER`) VALUES (CONCAT('081',rand()*100000000)) 

table layout

 CREATE TABLE IF NOT EXISTS `table1` ( `ID` int(11) DEFAULT NULL, `NUMBER` char(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;