Pad a number with leading zeros in SQL to make uniform char length
When you are in need of transforming a number to have some character to be prefixed in order to make the chars in the number a specific number long, here is one way to do it.
commit f8394c6 · authored · 1 min read
When you are in need of transforming a number to have zeros prefixed, use the below query. In order to prefix with zeros, we need to convert the number to a varchar.
SELECT REPLACE(STR(n,x),’ ’,‘y’)
STR function in SQL returns character data converted from numeric data. In the query, ‘n’ is the number to be transformed. ‘x’ is the number of chars to be returned and ‘y’ is the character to be prefixed with the number ‘n’ in order to make the specified length ‘x’.
eg. SELECT REPLACE(STR(9,5),’ ’,‘0’) will return “00009”
