Query for getting the range of values from two columns

Hi,

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?
java noviceAsked:
Who is Participating?
 
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:
SELECT *
FROM test_range
WHERE TRY_CAST([start] AS INT) IS NOT NULL AND TRY_CAST([end] AS INT) IS NOT NULL
	AND CAST([start] AS INT) >= 500 AND CAST([end] AS INT) <=1000

Open in new window

0
 
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.
0
 
java 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?
0
 
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.
0
 
awking00Commented:
Assuming the four records in your test_range table, tell us which records should be returned by the query and why.
0
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.