I have a table
Range is a string that can only have the values
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?
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.
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);