CODEWITHSUNDEEP
c#sqlsql-servert-sql
Edgar de Jesus Ceballos Garcia
Edgar de Jesus Ceballos Garcia

Reputation: 43

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

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.

Upvotes: 2

Views: 427

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

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+'-'))

Upvotes: 3

Related Questions