Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2009-04-07
4
270 Views
Last Modified: 2012-05-07
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:
SELECT x,y
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!
0
Comment
Question by:tobzzz
  • 2
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
tobzzz earned 0 total points
ID: 24091563
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!
0
 
LVL 42

Expert Comment

by:pcelba
ID: 24091574
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.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 24091635
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.
0
 
LVL 11

Author Comment

by:tobzzz
ID: 24092113
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.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL HELP 2 94
Sql Data via Excel--performance issues 2 56
Move SQL 2005 Express to Server 2012R2 19 143
date diff with Fiscal Calendar 4 55
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question