CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Michael Robinson
Michael Robinson

Reputation: 1142

String comparison in ms sql

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

Answers (4)

Joel Coehoorn
Joel Coehoorn

Reputation: 416179

Try this:

WHERE crse_code LIKE '8%'

Upvotes: 2

Mahesh Sambu
Mahesh Sambu

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

Mahesh Sambu
Mahesh Sambu

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

shree.pat18
shree.pat18

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

Related Questions