Reputation: 1142
I've got a table that has some course labels such as
Subject | Course |
ACC | 201
ACC | 843
ACC | 843I
ACC | 850
ACC | 930
I'm using this SQL to get ACC 843, ACC 843I and ACC850:
select Subj_Code, crse_code
from section_info
Where (crse_code between '800' and '899') and subj_code = 'ACC'
order by crse_code
But somehow, this misses the 843I. How can I get this check to work?
Thanks.
Upvotes: 0
Views: 1135
Reputation: 349
WITH Orderedcourses AS
(
SELECT Subj_Code, crse_code,
ROW_NUMBER() OVER (ORDER BY cast(left(crse_code,3) as int) )AS RowNumber
FROM Sales.SalesOrderHeader
)
SELECT Subj_Code, crse_code, RowNumber
FROM Orderedcourses
WHERE RowNumber BETWEEN '800' and '899';
This is A Common Table Expression which i have created to store the rownumber for sorting the column on course numbers and it is called by the query down there to get the ordered results of the column.
Upvotes: 0
Reputation: 349
select Subj_Code, crse_code
from section_info
Where (cast(left(crse_code,3) as int) as num between 800 and 899) and subj_code = 'ACC'
order by crse_code
Hope this works, try it out and let me know. Thanks!
Upvotes: 2
Reputation: 21757
Assuming that course name has a fixed format of 3 digits followed by an optional letter, you could simply compare on the first 3 characters of the column. Additionally, since the first 3 characters are always digits, you may want to convert them to numbers before comparing. Your query will then become something like this:
select Subj_Code, crse_code
from section_info
Where (cast(left(crse_code,3) as int) between 800 and 899) and subj_code = 'ACC'
order by crse_code
Upvotes: 2