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


comments powered by Disqus

About the author

  • Abhith Rajan

    Abhith Rajan

    Abhith Rajan is a full-stack developer, consultant, Sysberries employee, and biker. Who passionate about C# and JavaScript. Works on his blog, his personal finance web app and its react native mobile app in his free time.

    Buy me a coffeeBuy me a coffee