Check if a number is between a range using SQL & C#

Question

I have a table with the following info:

ID   Name                  Range          Checked
1    Jennifer             000-100           0
2    Louis                101-200           0
3    Michael              201-300           0

The range are the numbers of the tickets they have, and the checked column are the number of tickets that have been used.

What I want to do is to add +1 to the column Checked when a ticket is used, so I want to check where the ticket belongs. I mean, if I use ticket number 103, I want to add 1 to the column checked in the row number 2. And so on if I use more tickets.

ID   Name                  Range          Checked
1    Jennifer             000-100           0
2    Louis                101-200           1
3    Michael              201-300           0

So, is there a way to check if the ticket I have submitted is between one of the ranges?

PD.: I know how to check if a number is between two numbers in SQL, and I do also know how to get info from specific rows using C#, what I don't know how to do is to check the entire table to see if the number is between the ranges column.

It's probably already obvious for you, but you should never store several things into one column, like the range. You should have 2 number columns instead. — James Z
– James Z, Commented May 14, 2017 at 5:04

John Cappelletti · Accepted Answer · 2017-05-14 00:14:45Z

3

If the Range Values are 3 digits, left() and right() would do the trick without having to find the dash.

Example

Update YourTable 
   Set Checked=Checked+1
 Where 103 between left(Range,3) and Right(Range,3)

Select * from YourTable

Results

ID  Name        Range     Checked
1   Jennifer    000-100   0
2   Louis       101-200   1
3   Michael     201-300   0

EDIT - CharIndex() option For Variable Ranges

Update @YourTable 
   Set Checked=Checked+1
 Where 103 between left(Range,charindex('-',Range+'-')-1) and Right(Range,len(Range)-charindex('-',Range+'-'))

edited May 14, 2017 at 0:14

answered May 14, 2017 at 0:07

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

7 Comments

Felipe Oriani Over a year ago

Good solution but it answer limit to 3 chars between - char. Maybe using CHARINDEX function can provider a better solution.

John Cappelletti Over a year ago

@FelipeOriani Agreed, hence my top comment. However, you are correct so I will add the second option with charindex()

Edgar de Jesus Ceballos Garcia Over a year ago

Thanks very much. The first solution applies when is a range of 3 digits an the other one when are more than 3 digits?

John Cappelletti Over a year ago

@EdgardeJesusCeballosGarcia correct. If you only have 3 digits, then the first is more efficient. However, the second woul support fixed or variable.

Edgar de Jesus Ceballos Garcia Over a year ago

Thanks very much @JohnCappelletti

|

Collectives™ on Stack Overflow

Check if a number is between a range using SQL & C#

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related