Wednesday, April 25, 2012

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]
WHERE 
len (SomeColumn) =4
 
UPDATE  [SomeTable]
      SET SomeColumn = '00' + SomeColumn
WHERE 
len (SomeColumn) =3
 
UPDATE  [SomeTable]
      SET SomeColumn = '000' + SomeColumn
WHERE 
len (SomeColumn) = 2
 
UPDATE  [SomeTable]
      SET SomeColumn = '0000' + SomeColumn
WHERE 
len (SomeColumn) =1
 
UPDATE  [Some]
      SET SomeColumn = '00000'
WHERE 
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.