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 Rajan
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

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


Abhith Rajan

Written byAbhith Rajan
Abhith Rajan is a software engineer by day and a full-stack developer by night. He's coding for almost a decade now. He codes 🧑‍💻, write ✍️, learn 📖 and advocate 👍.
Connect

Is this page helpful?

Related SnippetsView All

Related ArticlesView All

Related VideosView All

High Availability and SLA for Azure SQL Managed Instance

Using SQL Data Sync for Bidirectional Data Replication in SQL Server & Azure SQL DBs | Data Exposed

Build fast, scalable data system on Azure SQL Database Hyperscale | Clearent

Related Tools & ServicesView All

sqlfum.pt

sequel fumpt

sqlfmt is an online SQL formatter. It is pronounced sequel fumpt. Its purpose is to beautifully format SQL statements.
SmarterASP.NET

SmarterASP.net - Unlimited ASP.NET Web Hosting

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