How to zero fill number with MSSQL

  • I often need to change the formatting of numbers, such as zero fill numbers, so that the numbers are always the same length, such as transforming 1 to 0001.
  • It's always better to control the look of the columns with the front-end, but sometimes you will need to do it in SQL Server.
  • You can use the cast with the right string functions for doing the job.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. USE sql911;
  2. go
  3. SELECT counter,emp_short,trans_date,trans_type, amount
  4. FROM trans01;
  5. go
counter     emp_short  trans_date              trans_type amount
----------- ---------- ----------------------- ---------- ---------------------
1           000010     2007-05-01 00:00:00     IN         345.00
11          000010     2007-05-02 00:00:00     IN         175.00
21          000010     2007-05-03 00:00:00     OUT        -75.00
31          000010     2007-05-05 00:00:00     OUT        -100.00
41          000030     2007-05-15 00:00:00     IN         10000.00
51          000030     2007-05-16 00:00:00     IN         435.00
61          000030     2007-05-20 00:00:00     OUT        -125.00
71          000030     2007-05-30 00:00:00     OUT        -1100.00
81          000110     2007-04-12 00:00:00     OUT        -250.00
91          000110     2007-04-30 00:00:00     OUT        -75.00
101         000110     2007-05-10 00:00:00     IN         1000.00
111         000190     2007-06-01 00:00:00     IN         99.00
121         000190     2007-06-01 00:00:00     IN         134.00
131         000310     2007-07-01 00:00:00     IN         1156.40
141         000310     2007-07-02 00:00:00     IN         67.00
151         000310     2007-08-01 00:00:00     OUT        -586.00

(16 row(s) affected)

Zero fill numbers

  1. SELECT RIGHT('0000' + CAST(counter AS VARCHAR(4)),4) AS 'Counter',
  2.        emp_short,trans_date,trans_type, amount
  3. FROM trans01;
  4. go
Counter emp_short  trans_date              trans_type amount
------- ---------- ----------------------- ---------- ---------------------
0001    000010     2007-05-01 00:00:00     IN         345.00
0011    000010     2007-05-02 00:00:00     IN         175.00
0021    000010     2007-05-03 00:00:00     OUT        -75.00
0031    000010     2007-05-05 00:00:00     OUT        -100.00
0041    000030     2007-05-15 00:00:00     IN         10000.00
0051    000030     2007-05-16 00:00:00     IN         435.00
0061    000030     2007-05-20 00:00:00     OUT        -125.00
0071    000030     2007-05-30 00:00:00     OUT        -1100.00
0081    000110     2007-04-12 00:00:00     OUT        -250.00
0091    000110     2007-04-30 00:00:00     OUT        -75.00
0101    000110     2007-05-10 00:00:00     IN         1000.00
0111    000190     2007-06-01 00:00:00     IN         99.00
0121    000190     2007-06-01 00:00:00     IN         134.00
0131    000310     2007-07-01 00:00:00     IN         1156.40
0141    000310     2007-07-02 00:00:00     IN         67.00
0151    000310     2007-08-01 00:00:00     OUT        -586.00

(16 row(s) affected)
  • Line 1: right('0000' + cast(counter as varchar(4)),4) is what will zero fill the column Counter.
  • You will need to know the maximum length of the number.
  • When you have decided on the length, you will zero fill the right to that full number of zeros.
  • You just have to set both the varchar and the right to the same length as the zero filled number.
  • This operation adds complexity to the query.
  • The zero fill should be done on the front end, receiving the data.