Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Adding Leading Zeros To Integer Values

From Wiki

Jump to: navigation, search

You have values in a column stored as integers but you would like to display 8 characters always. If you have 123 you would like to show 00000123. The trick is to use the RIGHT function, "the number of characters desired" + the column, "the number of characters desired". For example RIGHT("00000000' + Column,8)

Let's take a look, first create this table

  1. CREATE TABLE #Numbers(Num INT)
  2.     INSERT #Numbers VALUES('1')
  3.     INSERT #Numbers VALUES('12')
  4.     INSERT #Numbers VALUES('123')
  5.     INSERT #Numbers VALUES('1234')
  6.     INSERT #Numbers VALUES('12345')
  7.     INSERT #Numbers VALUES('123456')
  8.     INSERT #Numbers VALUES('1234567')
  9.     INSERT #Numbers VALUES('12345678')
  10.     INSERT #Numbers VALUES('123456789')


  1. SELECT RIGHT('00000000' + CONVERT(VARCHAR(8),Num),8)
  2.     FROM #Numbers


Output



00000001
00000012
00000123
00001234
00012345
00123456
01234567
12345678
0000000*

As you can see the last row has the value 0000000*. This is because converting to varchar(8) truncated the value. If we increase our convert and right functions to use 9 instead of 8 characters we are fine. Run the same query again.

  1. SELECT RIGHT('00000000' + CONVERT(VARCHAR(9),Num),9)
  2.     FROM #Numbers

output



000000001
000000012
000000123
000001234
000012345
000123456
001234567
012345678
123456789

What about negative values? What if you want to show -00000123 instead of -123? First insert these 4 rows

  1. INSERT #Numbers VALUES('-122')
  2.  INSERT #Numbers VALUES('-1')
  3.  INSERT #Numbers VALUES('-777777')
  4.  INSERT #Numbers VALUES('-123456789')

Now we will run the same query again

  1. SELECT RIGHT('00000000' + CONVERT(VARCHAR(9),Num),9)
  2.     FROM #Numbers

Here is what those 4 rows look like that we just inserted


(output)
00000-122
0000000-1
00-777777
00000000*


That is not good. Here is what we will do, if the number is negative we will start with a minus sign other wise a blank and then we will concatenate and replace the minus sign with a blank. This is what it looks like in SQL

  1. SELECT CASE  WHEN Num < 0
  2. THEN '-' ELSE '' END + RIGHT('000000000' + REPLACE(Num,'-',''), 9)
  3.  FROM #Numbers


output



000000001
000000012
000000123
000001234
000012345
000123456
001234567
012345678
123456789
-000000122
-000000001
-000777777
-123456789

As you can see it is not that difficult to do stuff like this

Contributed by: --SQLDenis 02:55, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Handy tricks

177 Rating: 2.8/5 (39 votes cast)