T-SQL: Adding Leading Zeros with a Simple Scalar Function

We were creating a extract at work from T-SQL and new that the we would need to export data with leading zeros. The data was not stored in the database with those leading zero values. The solution, create a simple scalar function to dynamically add the zeros that could accept two parameters ( text , and total length).

The Function is rather simple takes the total length value passed by the user and then subtracts the Length of the the text value.

What would make this better?

  • Would have a default value for total length
  • Error checking to make sure the text supplied is not longer than the total length


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jack C Donnell
-- Create date: 2008-04-28
-- Description: Adds leading Zeros to CHAR
-- =============================================
ALTER FUNCTION dbo.AddLeadingZeros
(
@textString NVARCHAR(100) ,@columnLEN int
)
RETURNS NVARCHAR(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultValue NVARCHAR(100)
,@i as INT
,@findLen as INT
-- Add the T-SQL statements to compute the return value here
SET @i = 1
SELECT @findLen = @columnLEN - LEN(@textString)
WHILE @i <= @findLen
BEGIN
SET @textString = '0' + @textString
SET @i = @i + 1
END
SET @ResultValue = @textstring
-- Return the result of the function
RETURN RTRIM(@ResultValue)
END
GO

How Used :

 

--Submit Character Value and Length

 

 

Select dbo.AddLeadingZeros(‘CVND’,15) as

[4 Alph Chars with 11 Zeros=15]

                   4 Alph Chars with 11 Zeros=15
                   —————————–
                   00000000000CVND

–Submit Numeric Value and Length

SELECT dbo.AddLeadingZeros(874598,12) as [6 Num with 6 Zeros=12]

 

                   6 Num with 6 Zeros=12
                   —————————–
                   000000874598

 

Tiny URL for this post:
 

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

WordPress Anti Spam by WP-SpamShield