Add a number in front of all records

Asked

Viewed 1,940 times

3

A question arose when mounting an sql statement. I have several records in a table called db_contrato, I wanted to add the number 0 in front of all records, for now I defined the contract column as varchar (50) .

db_contract 

id|contrato  
1 |3094  --> 03094   
2 |4058  --> 04058   
3 |2020  --> 02020  
4 |1620  --> 01620
mysql sql

  • Which bank are you using?

  • use the mysql database

  • if the number is 99, he had seen 099 or 00099?

  • if 99 turns 099

2 answers

7

If you want a fixed number of houses:

SELECT LPAD( campo, 6, '0') AS comzeros;

If you want a zero:

SELECT CONCAT( '0', campo) AS umzero;

If you have the problem of spaces before, you can do a CAST:

SELECT LPAD( CAST(campo AS UNSIGNED) ....

4


  • how do I update these records to get zero in front?

  • @Smokerohden something like update tabela set campo = concat(0, campo) where id = ?

  • thank you, solved!

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.