sql-server

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.

Abhith RajanOctober 01, 2018 · 1 min read · Last Updated:

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 Rajan
Abhith Rajan is an aspiring software engineer with more than 8 years of experience and proven successful track record of delivering technology-based products and services.
Buy me a coffee

Was this article helpful?

Your opinion matters

Please share your thought about this article

This page is open source. Noticed a typo? Or something unclear?
Improve this page on GitHub

Related Posts

Related Videos

Azure SQL Game Changers for Developers | BDL112

Related Stories

Related Tools & Services

SmarterASP.net - Unlimited ASP.NET Web Hosting

ASP.NET Hosting by SmarterASP.net. Unlimited ASP.NET Hosting Plans Starting at $2.95 a month.