sql-server ,

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

Abhith Rajan Abhith Rajan FollowOctober 01, 2018 · 1 min read · Last Updated:
Share this

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

Abhith Rajan
Written by Abhith Rajan FollowBuy me a coffee
Abhith Rajan is an aspiring software engineer with more than 6 years of experience and proven successful track record of delivering technology-based products and services.