Decimal with Leading Zero

• DougGifford

  SSCommitted

  Points: 1815

  More actions

  June 9, 2011 at 10:53 am

  #1336526

  Peter Maloof (6/9/2011)

  ---

  GSquared (6/7/2011)

  ---

  Credit card "numbers" aren't really numeric data, they're names with numbers instead of letters, if you look at them from a functional point of view. Nobody is ever going to add two credit card numbers together for anything meaningful, after all.

  I totally agree with this. Additionally, if you store a credit card number in a numeric data type, any leading zeros will be lost:

  create table NumberTest (creditCard numeric(10))

  go

  insert into NumberTest values (0123456789)

  select creditCard from NumberTest

  creditCard

  ---------------------------------------

  123456789

  In his Stairway to Data series, Joe Celko calls this type of number a Tag Number[/url].

  Same goes for SSN's!

  "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
  "Robert A. Heinlein"

• Peter Maloof

  SSCommitted

  Points: 1733

  More actions

  June 9, 2011 at 11:12 am

  #1336549

  Doug:

  Same goes for SSN's!

  True. I mentioned SSN's here, so I didn't want to be redundant again.

  ---

  Peter MaloofServing Data

• GSquared

  SSC Guru

  Points: 260824

  More actions

  June 9, 2011 at 12:21 pm

  #1336611

  There are lots of numbers like that. Zip codes, credit card numbers, driver's license numbers, SSNs, serial numbers for products, bar-code values, etc.

  All of these are good candidates for storage as user-defined data types based on char/varchar.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon