Check whether a number is in a string range

I have a table

ID	Count	Range
1	33	1-100
2	120	101-200
3	155	201-300

The Range is a string that can only have the values 1-100, 101-200, 201-300. I need to write a query that checks whether the count and the range fits, so 155 should pop up.

My idea was:

select * 
from Tabe
where Count between (select value from string_split(Range, '-'))

but this does not work. Can anyone help me out here?

Answer

Right off the bat I’m going to immediately suggest that you change your table structure to have separate columns for the lower and upper bounds of each range:

| ID | Count | lower_range | upper_range |
| 1  | 33    | 1           | 100         |
| 2  | 120   | 101         | 200         |
| 3  | 155   | 201         | 300         |

With this improved design, you only need a simple query:

SELECT *
FROM yourTable
WHERE Count BETWEEN lower_range AND upper_range;

The advantages of this approach is that the above query is completely sargable (i.e. can use an index, should a suitable one be defined). It also avoids your having to do some SQL olympics to isolate the lower and upper components of each hypenated range string. Since your intention here is to work with those numbers as numbers, just use the above design instead.

Edit:

Here is a query you may use if you are really stuck with this design:

SELECT *
FROM yourTable
WHERE
    Count BETWEEN CAST(SUBSTRING([Range], 1, CHARINDEX('-', [Range]) - 1) AS int) AND
                  CAST(SUBSTRING([Range], CHARINDEX('-', [Range]) + 1, LEN([Range])) AS int);

Demo