Leading zeros in SQL

So at work today, I ran across a SQL stored procedure called 'fixLeadingZeros' that has the below implementation.

It's actually much longer than this since it does it for several columns.... and some columns have longer values. In other words, if the field is supposed to have 8 digits, it has 8 statements.  I couldn't help but to feel a bit disgusted from it and had to share it.
UPDATE  [SomeTable]
      SET SomeColumn = '0' + [SomeColumn]
len (SomeColumn) =4
UPDATE  [SomeTable]
      SET SomeColumn = '00' + SomeColumn
len (SomeColumn) =3
UPDATE  [SomeTable]
      SET SomeColumn = '000' + SomeColumn
len (SomeColumn) = 2
UPDATE  [SomeTable]
      SET SomeColumn = '0000' + SomeColumn
len (SomeColumn) =1
UPDATE  [Some]
      SET SomeColumn = '00000'
SomeColumn ='' OR SomeColumn is null

This could have been done a LOT easier.  If your goal is to update existing columns so that they have leading zeros, all you'd need to do is this (assuming the column is a varchar type already and we wanted a 5 digit value):

UPDATE  [SomeTable]
SET SomeColumn = RIGHT('00000' + SomeColumn, 5)

What this does is specifies a string that has 5 zeros and the value of the column, ensuring that we have at least a string with 5 zeros.  Then we just want a subset of those values, from the right. In this case, we only want 5 characters from the right.

Let's say the value of SomeColumn is '123'.   We are then combining '00000' with '123' to give us '00000123'.

We then only take the 5 characters on the right, giving us '00123'.

Voila! Leading zeros.


  1. Merkur Slots Machines - SEGATIC PLAY - Singapore
    Merkur Slot 출장마사지 Machines. 5 https://septcasino.com/review/merit-casino/ star rating. The mens titanium wedding bands Merkur Casino game was the first to https://octcasino.com/ feature video slots wooricasinos.info in the entire casino,


Post a Comment

Popular posts from this blog

Using the BIQU Hermit Crab CAN

Add business days with moment.js