>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
Advertisements

Posted on May 24, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 1 Comment.

  1. >Or another way ….SELECT RIGHT('000000000' + SSN, 9) AS SSN FROM @EmpWhich will work for any combination of prefix

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: