CODEWITHSUNDEEP
mysql
Daniel Robertus
Daniel Robertus

Reputation: 1102

add some char to field until x field length MYSQL

let say i try this query 

select field1 from table1;

field1 is integer and return result like this : 

field1
---------
1
2
10
500

but i want to add zero until field1 is 3 char lengths like this 

field1  
----------
001     
002
010
500

for temporary i use this 

select if(field1<10,concat('00',field1),if(field1<100,concat('0',field1),field1)) from table1

do mysql have function for that? can i have shorter query?

Upvotes: 1

Views: 51

Answers (1)

juergen d
juergen d

Reputation: 204766

Use LPAD()

select LPAD(field1, 3, '0')
from table1

SQLFiddle demo

Upvotes: 5

Related Questions