sql query to check if the input value exists between range of values

Question

I am working in mssql database. I have the following scenario,

I have values in row as

2-5,10-12,67-89....

I need a sql query to fetch only those rows which has atleast one range that contains the user input.

For ex if the user input is 4, then there should be atleast one range that contains 4 like (1-5).

Is it possible to achieve the same with single query.

Does your field in the table contains 2-5 or it has two columns from and to where the values are 2 and 5 respectively — Sashi Kant
– Sashi Kant, Commented Dec 4, 2012 at 6:59
Are these ranges in the same rows or each range is in a separate row? — Mahmoud Gamal
– Mahmoud Gamal, Commented Dec 4, 2012 at 7:08

Mahmoud Gamal · Accepted Answer · 2012-12-04 07:19:42Z

3

Try this:

DECLARE @numberToFind INT = 4;
;WITH CTE
AS
(
    SELECT 
      arange,
      SUBSTRING(arange, 1, CHARINDEX('-', arange,1) - 1) "From",
      SUBSTRING(arange, 
                CHARINDEX('-', arange,1) + 1, 
                          LEN(arange) - CHARINDEX('-', arange,1) + 1) "To"
    FROM @ranges
) 
SELECT arange
FROM CTE
WHERE @numberToFind BETWEEN "From" AND "To";

SQL Fiddle Demo

If these ranges are stored as a single varchar string, then you have to parse these comma separated ranges first like so:

DECLARE @ranges VARCHAR(100) = ('2-5,10-12,67-89');
declare @numberToFind INT = 4;

DECLARE @Xml xml =  CONVERT(xml,
                            '<root><s>' + 
                            REPLACE(@ranges, ',', '</s><s>') + 
                            '</s></root>');

;WITH ParsedRanges
AS
(
    SELECT arange = T.c.value('.','varchar(20)')
    FROM @Xml.nodes('/root/s') T(c)
), CTE
AS
(
    SELECT 
      arange,
      SUBSTRING(arange, 1, CHARINDEX('-', arange,1) - 1) "From",
      SUBSTRING(arange, 
                CHARINDEX('-', arange,1) + 1, 
                LEN(arange) - CHARINDEX('-', arange,1) + 1) "To"
    FROM ParsedRanges
) 
SELECT arange
FROM CTE
WHERE @numberToFind BETWEEN "From" AND "To";

SQL Fiddle Demo

edited Dec 4, 2012 at 7:19

answered Dec 4, 2012 at 6:59

Mahmoud Gamal

80.3k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Anand B Over a year ago

Will it be possible to include this logic in a function. When I try to include it in a function it shows "Select statements included within a function cannot return data to a client." error

Mahmoud Gamal Over a year ago

@AnandB - Yes, it should be normally included in a function, you might have an error in your select query, please edit your question and show that function you tried. Also, if possible try to setup a demo for the function you tried in this site

Anand B Over a year ago

I am able to include the logic in function but I get "Invalid length paramter passed to substring "error when the ranges value has values without "-". For eg, @ranges VARCHAR(100) = ('2-5,15,10-12,67-89');

Anand B Over a year ago

I fixed the issue by checking for index of '-'

Anand B Over a year ago

There is a performance issue when the ranges value is large(near to varchar (6000)) or when the number of records is large in database. Will it be possible to check if numberToFind exists between "From" and "To" seperately for each "-" character instance and exit loop if match is found.

|

Collectives™ on Stack Overflow

sql query to check if the input value exists between range of values

1 Answer 1

SQL Fiddle Demo

SQL Fiddle Demo

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related