Should I quote numbers in SQL?

user_247306 0
Viewed 29833

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 can't find that quotation and I need to refresh my memory to see if I should quote numbers.

sql
7 Answers

You should not quote numbers if you want to treat them as 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.)

If you do this:

SELECT '10' AS x

instead you'll get a textual data type. This might too be suitable in some cases, but you need to decide whether you want the result as text or as a number.

user_2670

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 because the second comparison is performed using the current string collation rather than numerically.

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

user_2353540

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

John_Saunders_763370

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

user_2512540

eh... no you shouldn't?

I assume you mean by quoting enclosing in ' like 'this'

INSERT INTO table (foo) VALUES (999); is perfectly legal as long as foo is an INT type collumn INSERT INTO table (foo) VALUES('foo'); Inserts the string foo into the table. You can't do this on INT type tables of course.

user_2480650
Related