Simple T-SQL Quetion

Question

I have one table named "Student." I have the following columns in the table:-

I want to see all the students whose classes b/w V005 and V011. I don't want to use the "IN" clause in my query. I would like to use something similar to "Between". The data type of both columns is varchar. How can I write the query?

Answer 1

Hi @chuck DM

Try this:

Declare @table table(Student_Name varchar(20),Class varchar(20))

Insert into @table values
('AAA','V001'),('BBB','V003'),('CCC','V005'),('DDD','V007'),
('EEE','V009'),('FFF','V010'),('GGG','V011'),('HHH','V012')

SELECT *
FROM @table
WHERE RIGHT(Class,3) BETWEEN 5 AND 11

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can use string comparison as follow. You can combine it with LIKE to remove unexpected formats.

SELECT *
FROM Student
WHERE Class >= 'V005' AND Class <= 'V011'
  AND Class LIKE 'V0%'