Convert result of CASE Expression to a number in Oracle SQL

Question

I have the following select query. I want to convert it from string into a number. If I wrap a TO_NUMBER around the case expression, I get

expression must have same datatype as corresponding expression

error.

SELECT CASE SUBSTR(GRADE, INSTR(GRADE, ' ') + 1) 
         WHEN 'Unspecified' THEN ' '
         ELSE SUBSTR(GRADE, INSTR(GRADE, ' ') + 1) 
       END as Final_Grade,

How can I get Final_Grade to be numeric?

Thank you!

CAST or CONVERT = learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/… - stackoverflow.com/questions/728833/… — Inch High
– Inch High, Commented Feb 13, 2020 at 14:44
Does this answer your question? Convert a string to int using sql query — Inch High
– Inch High, Commented Feb 13, 2020 at 14:45
use TO_NUMBER( string1 [, format_mask] [, nls_language] ) and replace WHEN 'Unspecified' THEN ' ' with WHEN 'Unspecified' THEN -1, or any other number that make sense — Nicola Lepetit
– Nicola Lepetit, Commented Feb 13, 2020 at 14:47
How are your data samples? Do you want to extract digits from a string(GRADE)? Why do you need space characters? Btw, a case..when expression cannot start in such a way like CASE SUBSTR(GRADE, INSTR(GRADE, ' ') + 1) .. — Barbaros Özhan
– Barbaros Özhan, Commented Feb 13, 2020 at 15:00
Seems like GRADE contains non-numeric data. If you can't figure it out please post a reproducible test case including sample data. — APC
– APC, Commented Feb 13, 2020 at 15:01

Gordon Linoff · Accepted Answer · 2020-02-13 17:16:46Z

1

Well, ' ' is not a number, so better figure out what to do. I would suggest NULL:

SELECT (CASE SUBSTR(GRADE, INSTR(GRADE, ' ') + 1) 
           WHEN 'Unspecified' THEN NULL
           ELSE TO_NUMBER(SUBSTR(GRADE, INSTR(GRADE, ' ') + 1)) 
        END) as Final_Grade,

Actually, I prefer:

(CASE WHEN GRADE NOT LIKE 'Unspecified%'
      THEN TO_NUMBER(SUBSTR(GRADE, INSTR(GRADE, ' ') + 1)) 
 END) as Final_Grade

Or perhaps even more safely:

(CASE WHEN REGEXP_LIKE(GRADE, '^[0-9]+ ')
      THEN TO_NUMBER(SUBSTR(GRADE, INSTR(GRADE, ' ') + 1)) 
 END) as Final_Grade

answered Feb 13, 2020 at 17:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert result of CASE Expression to a number in Oracle SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related