CODEWITHSUNDEEP
sqloracle-database
shubham
shubham

Reputation: 43

convert letter to number in sql

I have a student table, one of its column contains the grade obtained by student(eg, A,B,C...). I need to assign a number to every grade (eg, A->10, B->8, C->6 ...)

I am new to sql and I am not getting any idea on how to do it. I tried one method which I found online:

select letter_grade, case when 'A' then 4 end as lettergrade from students;

But its not working

Upvotes: 0

Views: 2075

Answers (2)

user5683823
user5683823

Reputation:

An Oracle solution (which will not work in other SQL dialects, because it uses an Oracle proprietary function) is to use decode(). This function compares the first argument to the second, forth, sixth etc. and when it finds a match, it returns the argument immediately following the match. If no match is found, it returns the default entered as the last argument.

select student_id, letter_grade,
       decode(letter_grade, 'A', 10, 'B', 8, 'C', 6, 'D', 4, 0) as numeric_grade
from   students

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

You are on the right track:

SELECT
    letter_grade,
    CASE letter_grade WHEN 'A' THEN 10
                      WHEN 'B' THEN 8
                      WHEN 'C' THEN 6
                      WHEN 'D' THEN 4
                      ELSE 0 END AS number_grade
FROM students;

Upvotes: 4

Related Questions