Could someone please explain to me how arithmetic operators such as > or < compare strings in SQL Server 2005?
+1
A:
If they are against a numeric filed they work about as you would expect. If against a chacter type of data field they would work alphabetically. So if you wanted >10 and less than 20 you might get 11, 110, 123456 in response.
This is one reson why it is alwys a bad idea to store nnumbers you want ot be able to do math calcluations or comparisions with as varchar (Or any other type of char data). Only numbers that do not need math calculations (phone, zip, ssn, etc) should be stored that way and those should always be stored in a character type filed to account for leading zeros.
HLGEM
2009-01-13 16:32:45
+1
A:
Sorting (and thus the < > operators) depends on your SQL Server collation: http://msdn.microsoft.com/en-us/library/ms144250(SQL.90).aspx
And the rules for that collation you'd have to find on the web. Note that they are often very complicated. For your sake, I'd try and avoid < and > comparisons of VARCHAR's unless you really, really need to.
Matt Rogish
2009-01-13 17:04:26
+1
A:
Thanks for the quick response.
The reason that I'm asking is that I need to query a database table of UK postcodes. The table in question has two postcode columns, "from_postcode" and "to_postcode" and I need to determine whether a given postcode lies between these values.
Fortunately the data has been laid out in such a way that I think I should be able to use the arithmetic operators to at least get a subset of rows of potential matches.
Time for me to write a few (or perhaps a fair few) uni tests!