How to: Pad a Number with Leading Zeros

As mentioned in the previous post "What do NDC, NCPDP, NABP, NPI mean? ", sometimes a column requires a fixed length, such as NDC is a 11-digit number. If the source file does not provide a fixed-length number and the data is already loaded to your database, you need to message the column by padding it with leading zeros.

Here are two solutions by using SQL scripts or by using user-defined functions (UDFs).

• Solution 1:

             UPDATE [tableName]
             SET NDC = RIGHT(REPLICATE('0', 11) + CONVERT(VARCHAR,NDC),11)

• Solution 2:
• Be careful that length of NULL string is NULL.
• CREATE FUNCTION dbo.PadLeft
  (@str INT, @PadWidth INT, @PadChar CHAR(1))
  RETURNS VARCHAR(255)
  AS
  BEGIN
  RETURN RIGHT(REPLICATE(@PadChar, @PadWidth) + CONVERT(VARCHAR,@str),@PadWidth)
  END
  GO
  
• After creating the function above, you can test it with "select dbo.PadLeft(12345, 11, '0')". The result should be "00000012345".