Tuesday, May 24, 2011

SQL Server: Column Value with Leading Zeros


Till date I was using old CASE option to add zeros before a digit to get all values with same length for columns like social security number but today SQL Expert Shakeeb Younas shared a very simple method to achieve above mentioned goal.
DECLARE @Emp TABLE ( SSN VARCHAR(9) )
INSERT  INTO @Emp
        SELECT  '1'
        UNION ALL
        SELECT  '654'
        UNION ALL
        SELECT  '824741'
        UNION ALL
        SELECT  '123456789'
        UNION ALL
        SELECT  '37'

-- Get SSN with leading zeros       
SELECT  REPLACE(STR(SSN, 9), ' ', '0') AS SSN
FROM    @Emp

1 comment:

  1. Or another way ....

    SELECT RIGHT('000000000' + SSN, 9) AS SSN
    FROM @Emp

    Which will work for any combination of prefix

    ReplyDelete

All suggestions are welcome