4

Which data type I should use to store an 11 digit number? It must store '0' as the first digit.

For example, if I store 02421402015, it must show 02421402015. I tried using varchar and it shows 2421402015.

3
  • 8
    If you store 01234 in a varchar, it will show 01234 unless you are doing something else with it (e.g. an implicit conversion to a number in SQL Server, or some assumption by presentation code). Can you show the code that leads to this output? And can you explain why you need to store the leading 0 when this is something that is trivial to add to the number at presentation time? Feb 4, 2016 at 20:21
  • 2
    Can you provide sample code? If you stored 02421402015 in varchar(11), it would definitely be stored with the leading zero. It's only when you select it back out and try to convert it to an integer that it will strip the leading zero.
    – paulbarbin
    Feb 4, 2016 at 20:21
  • 4
    Please clarify the intended usage of this value. A value that has digits only but allows for leading zeros is not a "number": it is a string of digits. Zipcodes fall into this category as they can have leading zeros. Is the issue only with display the value, or will someone need to filter on 0123? Feb 4, 2016 at 20:29

2 Answers 2

14

I would suggest storing the number as a BIGINT, and simply displaying the number the way you want it.

DECLARE @Num BIGINT;

SET @Num = 2421402015;

SELECT CONVERT(VARCHAR(11), REPLICATE('0', 11 - LEN(@Num))) 
    + CONVERT(VARCHAR(11), @Num);

As pointed out by Aaron Bertrand in his comment, it is better performance-wise to not calculate the length of the required string of zeros, and simply truncate the string:

SELECT RIGHT(REPLICATE('0', 11) + CONVERT(VARCHAR(11), @Num), 11);

For your sample number, the concatenation would result in '000000000002421402015', which would then be right-truncated into 11 digits resulting in:

02421402015

In fact, as srutzky pointed out, this can be further optimized to:

SELECT RIGHT('00000000000' + CONVERT(VARCHAR(11), @Num), 11);

If users will pass in values with leading zeros that need to get matched in a WHERE clause, I would convert the search term to an integer first, then use that in the predicate, so an index can be used.

I've written a blog post1 that details how you can store the number as an integer, while simultaneously providing an elegant method for the number to look like it's being stored in the database with leading zeros. Check http://www.sqlserver.science/basics/how-do-i-store-a-number-with-leading-zeros/ for the details! There are several other methods of formatting the leading zeros column, however the performance of each method varies quite drastically; the RIGHT('00000000000' + CONVERT(VARCHAR(11), @Num), 11) method looks like it's the fastest by far.


1 - My blog post was featured in the SQL PASS Community Connector News
2 - Follow up post on performance of FORMATMESSAGE() function in SQL Server 2012+

0
6

To demonstrate that what you're saying in your question is not true:

DECLARE @x TABLE(y VARCHAR(11));

INSERT @x(y) VALUES('02421402015');

SELECT y FROM @x;

Results:

y
-----------
02421402015

You need to tell us more details about what is going on so we can understand your problem better.

But most importantly, you need to consider separating storage from presentation. It is far more efficient to store a number as an int (4 bytes) or bigint (8 bytes) as opposed to a varchar(11) (11 bytes + 2 bytes to indicate length), and you also get built-in validation (so someone can't store I'm varchar instead of a number), arithmetic, etc. without implicit or explicit conversion becoming a necessity. A leading 0 for display is trivial to add in your presentation tier, or even in SQL Server at query time if you really must. It is also trivial to add to or remove from incoming search criteria so that you're searching against the right and most efficient data types, using the right parameter type, regardless of what the user thinks they're searching and seeing...

0

Not the answer you're looking for? Browse other questions tagged or ask your own question.