October 25, 2016 at 10:43 am
Hey all,
we were running a report that was pulling (in short) any record that had a result_text > '7.9%'. The field 'result_text' is a varchar field. It was pulling anyone between 8.0 and 9.9, but excluding anyone above that number. There is no % stored in that field, FWIW.
This isn't something that we had actually set up, we just came across the issue. I've figured out that there is another field (just 'result', which is a numeric type) to use. So, I set that filter to result > '7.9' and it works swimmingly.
My question is: why were the results that were previously showing between 8 and 9.9? Is it because you can't really have a 'greater than' for text? If so, then why was it able to pull that range, but seemingly nothing 10 and above?
I understand this may not be enough info to give a good answer, but figured I'd try.
Thanks for your time!
October 25, 2016 at 11:14 am
scarr030 (10/25/2016)
...pulling (in short) any record that had a result_text > '7.9%'. The field 'result_text' is a varchar field. It was pulling anyone between 8.0 and 9.9, but excluding anyone above that number. My question is: why were the results that were previously showing between 8 and 9.9? Is it because you can't really have a 'greater than' for text? If so, then why was it able to pull that range, but seemingly nothing 10 and above?
if you're using less than or greater than comparisons with character data, then it's comparing ASCII values of the result_text data from left to right. since ASCII('7') = 55 and ASCII('1') = 49, 49 is less than 55 not greater.
October 25, 2016 at 11:37 am
It's not including only values from 8.0 to 9.9. It's also not comparing ASCII values.
It's ordering the values as a dictionary would do (but with more characters) and the order will vary depending on the collation used.
Basically, it will look for anything that would come after that value if the values were entries on a dictionary.
DECLARE @Sample TABLE(
result_text varchar(10)
);
INSERT INTO @Sample
VALUES( '5.3'),
( '7.9'),
( '7.95'),
( '8.6'),
( '15.7'),
( '88.3'),
( 'N/A');
SELECT *
FROM @Sample
WHERE result_text > '7.9%';
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply