Need to overcome why SQL select cannot find range between tens of thousands and hundreds of thousands

If I run:
SELECT postcode
FROM uk_postcodes
WHERE (x BETWEEN '422813.12' AND '487186.88')
     AND (y BETWEEN '75113.12' AND '139486.88')
I get zero results. Which is not true because If I run:
FROM uk_postcodes
WHERE postcode='PO16'
I get this result x = 459,200 y = 106,000
By eye I can see this is in range. So, I changed the last line of my SQL to
      AND (y BETWEEN '100113.12' AND '139486.88')
Notice the low range has changed from 75113.12 to 100113.12 so it is not tens of thousands but hundreds of thousands and then for whatever reason it finds results!!!! How frustrating.

Anyway, is this a known bug? Is there a work-around? Do I just need to format the number somehow so SQL Server 2005 can recognise it?

Many thanks!
LVL 11
Who is Participating?
tobzzzConnect With a Mentor Author Commented:
OK, nevermind, I just figured out if I put a 0 before the 75113.12 to make it 075113.12 then MS SQL Server processes my select properly and gives me results. No idea why but it works so I'll just format the number to have 0's in front to match the length of the greater number. Weird!
Is x and y numeric? Try this:

SELECT postcode
FROM uk_postcodes
WHERE (x BETWEEN 422813.12 AND 487186.88)
     AND (y BETWEEN 75113.12 AND 139486.88)

It it is not numeric you have to adjust text strings to the same length by '0' prefix.
Just to explain - there can be nothing BETWEEN two text strings '75113.12' AND '139486.88' because the first STRING is greater than the second one. How SQL Server should know you would like to compare numbers? Look at implicit data conversions in help.
tobzzzAuthor Commented:
Hi pcelba, thanks for responding. As soon as I noticed adding a 0 to the beginning worked I realised that I was not dealing with a numeric string that should obviously be numberic. So, I converted the field data type to numeric and amended by SQL. It worked perfectly.

Thanks again for your response anyway.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.