Use CONCAT to adjust the amount of php mysql numbers

4

I need to complete the amount of numbers to display on the screen, what would that be. the quantity that would be an example: 20 need to enter 15 characters with 0,

Example: 000000000000020 .

A friend of mine showed me in Excel a way he uses the CONCAT and then only takes the last 15 numbers, so the id can be 1,100,100 etc ... that it will always be 15 numbers that would be the zeros plus the quantity.

I looked here on the site and also on the internet but could not do what I need, someone would have a tip.

This result I bring from MySQL, if it is easier on MySQL I can use it as well.

Here is an example of the query I tried to do:

SELECT 
 MAX(cup_id)             AS CUPOM,
 MAX(DATE_FORMAT(c.cup_data_hora, '%d/%m/%Y'))    AS DATA,
 MAX(c.cup_data_hora)    AS HORA,
 MAX(l.loja_num)         AS LOJA_NUM,
 MAX(l.loja_desc)        AS LOJA_NOME,
 MAX(l.loja_end)         AS END_LOJA,
 MAX(u.usu_cod)          AS USU_ID,
 MAX(u.usu_nome)         AS USU_NOME,
 MAX(v.vas_cod_emporium) AS COD_EMPORIUM,
 MAX( v.vas_desc)        AS VASILHAME,
 MAX(c.cup_quantidade)   AS QUANTIDADE,
 CONCAT(c.cup_quantidade,'000000000000') AS QUANTIDADE_15
FROM cupom  AS c
 LEFT JOIN loja      AS l ON c.cup_loja      = l.loja_num
 LEFT JOIN vasilhame AS v ON c.cup_vasilhame = v.vas_id
 LEFT JOIN usuarios  AS u ON c.cup_usu_id    = u.usu_cod
   WHERE cup_loja =4** 
    
asked by anonymous 03.05.2016 / 20:45

1 answer

10

Use the lpad() MySQL function to add a 'prefix' to the left of the string, the first argument can be the field that will be modified, the second is the length (4 characters) and the last one is the character that will be added.

SELECT lpad('1',4,0)
SELECT lpad('10',4,0)
SELECT lpad('100',4,0)
SELECT lpad('1000',4,0)

The outputs are:

0001 
0010
0100
1000

Example - sqlfiddle

    
03.05.2016 / 20:51