I was recently asked to append some leading zeros to a column in a data set. I found one solution which was quick and simple….
I’ll use the Adventureworks2012 database to demonstrate and lets say that we want the month of the OrderDate column to be formatted as 01 to 12:
|
1 2 3 4 5 6 |
SELECT OrderDate, MONTH(OrderDate) AS MonthInteger, RIGHT('0' + CONVERT(NVARCHAR(2),MONTH(OrderDate)), 2) AS MonthIntegerWithZero FROM AdventureWorks2012.Sales.SalesOrderHeader; |
Whats going on then?
We pass the OrderDate column into the MONTH() function so that we can get an integer value that will be between 1 and 12, however, it will not have a leading zero for the values between 1 and 9. Next we convert the integer into a string value by using CONVERT() with NVARCHAR(2) – this way we don’t truncate any two digit numbers that we have so far (10,11 & 12). We append ‘0’ onto the front of our new string and now we use the RIGHT() function which will return the number of digits from the right side of the string, in our case we are saying two!
We use the RIGHT() to return our final value because when we append the ‘0’ to our string the two digit values become 010, 011 & 012, however, we want just two digits.
This is a partial data set with a DISTINCT on it so you can see different results:
|
1 2 3 4 5 6 7 8 9 |
OrderDate MonthInteger MonthIntegerWithZero ----------------------- ------------ -------------------- 2006-02-12 00:00:00.000 2 02 2008-03-07 00:00:00.000 3 03 2006-04-28 00:00:00.000 4 04 2005-07-26 00:00:00.000 7 07 2008-03-14 00:00:00.000 3 03 2008-07-24 00:00:00.000 7 07 2006-12-31 00:00:00.000 12 12 |
I decided to save the above code into a Scalar UDF when I found a UDF in the AdventureWorks2012 database that already has this functionality implemented in a different way! I liked the way the way that the UDF works and decided to tweak it a little, here is the code to create the UDF:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
USE [AdventureWorks2012] GO /****** Object: UserDefinedFunction [dbo].[ufnLeadingZeros] Script Date: 04/10/2015 08:41:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufnLotsOfLeadingZeros]( @Value int, @LengthOfOutput int ) RETURNS varchar(100) WITH SCHEMABINDING AS BEGIN DECLARE @ReturnValue varchar(100); SET @ReturnValue = CONVERT(varchar(100), @Value); SET @ReturnValue = REPLICATE('0', @LengthOfOutput - DATALENGTH(@ReturnValue)) + @ReturnValue; RETURN (@ReturnValue); END; |
What you can do is pass an integer value into this UDF and then pass in another integer which is the total length of the string that you want to return, if you want more that 100 leading zeros then you’ll need to change the return data length and the ReturnValue data length.
What it’s doing is repeating / replicating the 0 the number of times that you passed in minus the length of the value you passed in, it then appends the value to the end.
Leave a Comment