Skip to main content

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.



Comments

  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,

    ReplyDelete

Post a Comment

Popular posts from this blog

Using the BIQU Hermit Crab CAN

I was recently offered to try out the BIQU Hermit Crab CAN.  This was great timing as one of my printers was down due to a bad thermistor cable in the cable drag chain.  I was in the process of removing the wiring and also replacing the drag chain to an open style which allows me to open up each segment and easily add or remove wires.   What is the Hermit Crab? The Hermit Crab is a quick change tool, which allows you to easily and quickly change out your tool. For example, you can change to a FDM extruder, or a laser engraver, or a cutting tool, or whatever you want. The CAN version uses just one USB-C cable (doesn't use USB protocol, just uses that style cable for convenience). The USB-C cable carries the data and power to the hotend. The CAN version has a bunch of nice features such as accelerometer, Neopixel RGB LEDs, and TMC2209 stepper driver. Current setup In this photo, you can see my current setup as I'm in the process of rewiring.  I've got an Orbiter extruder with

Debugging .NET Windows Service apps more easily

Here at work, I keep seeing a couple of my fellow developers struggle to debug their Windows Service apps that they're writing.  What they have to do is remove the service, install the new version of the service, start the service, then attach the debugger. If there's an error when the service first starts up, the workaround is to introduce a Thread.Sleep before it actually does anything, giving you enough time to attach the debugger. By default, you can't directly run the service from within Visual Studio. If you try, you get the following message: If you look at the code that actually starts the service, you'll see that it's not that different from a console app. By default, it looks something like this:      internal   static   class   Program     {          ///   <summary>          ///  The main entry point for the application.          ///   </summary>          private   static   void  Main()         {              ServiceBase [] Ser