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

Add business days with moment.js

Date . prototype . addBusinessDays = function ( businessDays : number ) { // 5 business days in a week const weeks = Math . floor ( businessDays / 5) ; // Convert business days (5 days in a week) to regular days (7 days in a week) const days = weeks * 7 ; const date = moment ( this ) ; // Convert the incoming date value to a moment value. const newDate = moment ( this ) . add ( days + ( businessDays % 5) , 'days' ) ; if ( newDate . day () < date . day () || newDate . day () % 6 === 0) { // Add 2 more days if we land on a weekend, or we went through a weekend. newDate . add (2 , 'days' ) ; // .add is not pure and will change the underlying value } return newDate . toDate () ; } ;

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