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).<\/p>\n
The Function is rather simple takes the total length value passed by the user and then subtracts the Length of the the text value.<\/p>\n
What would make this better?<\/p>\n
<\/p>\n
\u00a0<\/p>\n \u00a0<\/p>\n \u00a0<\/p>\n Select dbo.AddLeadingZeros(‘CVND’,15) as<\/p>\n [4 Alph Chars with 11 Zeros=15]<\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4 Alph Chars with 11 Zeros=15 –Submit Numeric Value and Length<\/p>\n SELECT dbo.AddLeadingZeros(874598,12) as [6 Num with 6 Zeros=12]<\/p>\n \u00a0<\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6 Num with 6 Zeros=12 \u00a0<\/p>\n \ufffd<\/p>\n","protected":false},"excerpt":{"rendered":" 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 …<\/p>\n
\nSET ANSI_NULLS ON
\nGO
\nSET QUOTED_IDENTIFIER ON
\nGO
\n-- =============================================
\n-- Author: Jack C Donnell
\n-- Create date: 2008-04-28
\n-- Description: Adds leading Zeros to CHAR
\n-- =============================================
\nALTER FUNCTION dbo.AddLeadingZeros
\n(
\n@textString NVARCHAR(100) ,@columnLEN int
\n)
\nRETURNS NVARCHAR(100)
\nAS
\nBEGIN
\n-- Declare the return variable here
\nDECLARE @ResultValue NVARCHAR(100)
\n,@i as INT
\n,@findLen as INT
\n-- Add the T-SQL statements to compute the return value here
\nSET @i = 1
\nSELECT @findLen = @columnLEN - LEN(@textString)
\nWHILE @i <= @findLen
\nBEGIN
\nSET @textString = '0' + @textString
\nSET @i = @i + 1
\nEND
\nSET @ResultValue = @textstring
\n-- Return the result of the function
\nRETURN RTRIM(@ResultValue)
\nEND
\nGO
\n<\/code><\/p>\nHow Used :<\/code><\/div>\n--Submit Character Value and Length<\/code><\/div>\n
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 —————————–
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00000000000CVND<\/p>\n
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 —————————–
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 000000874598<\/p>\n