Pad a number with leading zeros in SQL to make uniform char length

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”

Additional Resources

Written by Abhith RajanFollow @AbhithRajan
Abhith Rajan is an aspiring software engineer with more than 7 years of experience and proven successful track record of delivering technology-based products and services.
Buy me a coffee ☕