Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I have inserted two records into a table.

create table num(id int)
insert into num values(0023)
insert into num values(23)
select * from num

When I query them, they all display as 23. It means that SQL Server ignores the leading 0s. What's the mechanism behind that? How can I have SQL Server return the values as I inserted them (i.e. 0023 and 23)?

share|improve this question
2  
Why do you care about the leading zeros? If they are meaningful to your system, then id should be of type VARCHAR or similar. – Nick Chammas Mar 15 '12 at 15:17
Exactly how do you expect 0023 to be encoded in the database as something different from 23 or 023 or 0000000023? They all represent the same NUMBER. And int is a NUMBER data type. There's no place for the server to store this "leading number of decimal zeroes" value. – ErikE Mar 15 '12 at 23:57
This is a poor question, and would have been covered in any introduction to programming class. An int can never store leading zeros, because of the nature of ints. This can be solved by looking at any number of web resources. This does not need the input of a database administrator. – jcolebrand♦ Mar 20 '12 at 14:57

closed as off topic by jcolebrand♦ Mar 20 '12 at 14:57

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined in the FAQ. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about closed questions here.

3 Answers

up vote 16 down vote

0023 is not a number. It's a string. 23 is the number. SQL Server is able to recognize that those extra zeros are not needed to define the number, so it ignores them. If you want to dispaly a value as 0023 to the application, I'd suggest doing the formatting on the application side. That way the number stored in SQL Server is still a number if you want do addition, aggregation, or other calculations. If you really have to store it as '0023', you need to convert it to a character field; char, varchar, nvarchar, nchar.

share|improve this answer
If the 0023 is string,how can I insert the value into the table which I have defined the id as int format? – user8365 Mar 15 '12 at 13:23
@user8365 - Either define id as VARCHAR or just insert 23 instead of 0023 – Lamak Mar 15 '12 at 13:47
4  
@user8365 - You're asking if you can make SQL Server violate the domain integrity of that field. You can't. If 0023 is a string then store it as a string. If it isn't, then store it as an INT and forget about the leading zeros. – Nick Chammas Mar 15 '12 at 15:16
Exactly what @NickChammas said. You don't have any choice here. 23 is a number, 0023 is a string. If you want a number, treat it as a number. Like my answer says, if it needs to sort like a number, add, subtract, multiply, divide, etc., like a number, then it needs to be a number. No choices. No arguments. Leading zeros is just formatting. Do that in app code or somewhere. – Grant Fritchey Mar 16 '12 at 10:55
@Grant I cannot agree that the leading zeroes are just formatting. They are legitimate in any numeral system; however, 0023b10 = 23b10; so, any storage system gains some degree of compression by not encoding all the leading zeroes. So, fundamentally our questioner is asking the wrong question. Why is 0023 not an integer? It is an integer! We just don't need to encode an infinity of leading numerals to represent it as such. – ooutwire Mar 17 '12 at 0:36
show 2 more comments
up vote 3 down vote

It was already said in other answers that 00023 is a number; I just want to add that you can use computed columns to show that number using custom format. For example, 

create table num_table(id int not null primary key identity(1,1),
num int, leading_zeros smallint,
constraint chk_leading_zero_nonnegative check (leading_zero>=0),
num_formatted as replicate('0',coalesce(leading_zeros,0)) +cast(num as varchar(10)));
insert into num_table(num,leading_zeros) values(23,2) ;
select num_formatted from num_table; -- output '0023'
share|improve this answer
Good point, absolutely worth noting. – Grant Fritchey Mar 16 '12 at 10:57
up vote 0 down vote

0023 is a number but the int and other number data types do not store leading zeroes since there is no mathematical reason to do so.

If you need to store leading zeroes use a string data type like, char, varchar etc

share|improve this answer
If we assume that INT is fixed-length (like 32 bits) and binary notation is used for storing them, then leading zeroes are indeed stored. But they are not displayed in the output. – ypercube Mar 15 '12 at 15:35
@ypercube - Right, but the number of leading zeros is simply what's required to fill the 32 bits (as opposed to user-definable). – Nick Chammas Mar 15 '12 at 15:38
@Nick: Yeah, no argue there. I think the point is NOT whether or not leading zeroes are stored. It's whether two versions of the same number can be stored in the datatype, one with and one without leading zeroes. – ypercube Mar 15 '12 at 15:41
But this makes no sense. Two decimal leading zeroes has absolutely no relation to the number of leading zeroes in a 32-bit integer. Consider the decimal number 15--this only takes one digit in hexadecimal, F. They already have a different number of "leading zeroes." 2147483647 is 10 digits, but in hex that's only 7FFFFFFF or 8 digits. – ErikE Mar 16 '12 at 0:00
Fundamentally, one must consider the mathematical numeral system which we are using...in this case decimal (base 10). 23 is a decimal number; it is 0x1000 + 0x100 + 2x10 + 3 = 23. In octal, it is 2X8 + 3 and so on. So, to say to the storage engine, "store 23 with two leading zeroes" is not useful, since it merely encodes the same final result, that is 23. SQL Server does not ignore your zeroes, it merely returns to you a decimal value equal to the number which you inserted, i.e., 0023 or 23. – ooutwire Mar 17 '12 at 0:30

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