Query for getting the range of values from two columns


I have a table which has to columns namely start range and end range with datatype as varchar(200) receptively. There are thousands records, now i have to filter out the records which are withing the range of 500-1000? How can i achieve this using sql query?
Tech NoviceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
No usable data posted to test with, but I think this is it, for
within the range of 500-1000:

WHERE 1 = CASE WHEN start_range LIKE '%[^0-9]%' THEN 0
    WHEN start_range <= 1000 THEN 1 ELSE 0 END
AND 1 = CASE WHEN end_range LIKE '%[^0-9]%' THEN 0
    WHEN end_range >= 500 THEN 1 ELSE 0 END

Btw, the LIKE condition is to screen out any entry that's not an integer value.
Tech NoviceAuthor Commented:
create table test_range ([start] varchar(200),[end] varchar(200))

insert into test_range values('200','400')
insert into test_range values('500','600')
insert into test_range values('Abc200','b500')
insert into test_range values('x900','900')

select * from test_range
WHERE 1 = CASE WHEN [start] LIKE '%[^0-9]%' THEN 0
    WHEN [start] <= 'Abc200' THEN 1 ELSE 0 END
AND 1 = CASE WHEN [end] LIKE '%[^0-9]%' THEN 0
    WHEN [end] >= 'b500' THEN 1 ELSE 0 END

Not working in case of alphanumeric values?
Vitor MontalvãoMSSQL Senior EngineerCommented:
The problem is that you don't have only numeric characters so you'll need to convert them. Check if this solution works for you:
FROM test_range
	AND CAST([start] AS INT) >= 500 AND CAST([end] AS INT) <=1000

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Not working in case of alphanumeric values?

Sorry about that, I misunderstood your requirements.  When you wrote " 500-1000 " I thought you wanted only numeric ranges, so my original code ignores non-numeric table values.  [You have to remember, we have NO knowledge of your data NOR your search needs.  We can go ONLY by what you explicitly tell us.]

Alphanumeric could be coded back in, but you be aware you may not get the results you expect from those comparisons.
awking00Information Technology SpecialistCommented:
Assuming the four records in your test_range table, tell us which records should be returned by the query and why.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.