Should I Quote Numbers in SQL?

Question

I remember reading about quoting stuff when doing a sql query and that when you quote something, it becomes a string. I also read that numbers should not be quoted. Now, I cant find that quotation and I need to refresh my memory to see if I should quote numbers.

Answer 1

You should not quote numbers.

You're correct by remembering that it makes it a string.

SELECT 10 AS x

is perfectly legal and will return (in most database engines) a column of datatype int (or a variation thereof.)

I notice the comment to your question by Adam, and I'd like to make a comment to that, but I'll edit this answer because I think it's an important point.

Quoting "numbers" in SQL is usually done when it is not really a number, but instead a code.

By a number I mean something you can count, sum, calculate with, a code is an identifier.

So a SSN is a code, it's your unique code (I know, the N stands for "number", bit of a misnomer if you ask me), but you wouldn't try to calculate the average of all the SSN's in the database.

If you store product ID's in the database as strings, but in reality they are only made up of digits, then they're codes, not numbers.

Codes should be quoted, numbers should not.

Answer 2

I don't know what you may have read, but don't quote numbers.

Answer 3

here's an example, where quoting would produce inconsistent results (in mysql):

select 1 < 1.0;      // returns 0

select '1' < '1.0';  // returns 1

that's cause the second comparison is performed using the current string collation rather than numerically.

better not to quote numerics, since that would just be an extra unnecessary step for the db to convert the string literal into a numeric value for comparison and could alter the meaning of comparisons.

Answer 4

Obviously, don't forget to check to make sure any value you passed is really a number.