How do I return null if a CAST fails?

Question

In the SELECT statement below I'm casting a varchar2 to an integer value. How do I return NULL if the cast fails? The CAST would fail if the text, e.g. 'dog' cannot be cast to a integer. The column contains integers stored as strings (varchar2) and NULL.

SELECT CAST(text_column AS NUMBER(*,0)) column_alias
FROM schema.table@dblink;

Update - SELECT * FROM V$VERSION; reveals Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

@MT0 - this thread is a good illustration of the biggest flaw in the "marked as duplicate" concept. As shown in Thomas's answer, the question has a new answer in Oracle 12.2. Happily you didn't get a chance to mark the question as duplicate before he posted his answer; it is unlikely that he would have added it to a thread from several years ago. I am not suggesting the question shouldn't be marked as duplicate, I am just raising again this issue - of potentially new answers to old questions. — , Sep 20 '17 at 19:43

score 2 · Accepted Answer · answered Sep 20 '17 at 17:58

You need to check if the value is indeed an integer and handle it yourself; there is no "error handling" in SQL, as there is in procedural languages.

For example, if you don't have too much data, you may be fine with a check using regular expressions.

Something like

select cast ( case when regexp_like(text_column, '^(+|-)?\d+$') 
                   then text_column    -- else NULL;  this is the default anyway
              end 
              as number(*,0)
            ) as column_alias
from .......

This is nice when you need to specify precision and scale (something TO_NUMBER is incapable of, far as I can tell) — Luke, Aug 23 '22 at 19:15

score 2 · Answer 2 · answered Sep 20 '17 at 18:12

2

If you are using Oracle 12.2 you can use TO_NUMBER with the new ON CONVERSION ERROR clause. See http://docs.oracle.com/database/122/SQLRF/TO_NUMBER.htm#SQLRF06140

Example:

select to_number('dog' default null on conversion error) from dual;

answered Sep 20 '17 at 18:12

Thomas Aregger

520
3
8

2

Wow - I wasn't aware of this. Good to know! Perhaps not exactly what the OP needed (CAST has some effects that TO_NUMBER does not, for example when used in CREATE TABLE AS SELECT...), but as a general matter, this directly disproves what I said about "SQL having no error handling." This is 100% error handling within Oracle SQL. Upvote for sure! – Sep 20 '17 at 19:34
1

... and on further inspection it seems the CAST function has the same option! – Sep 20 '17 at 19:38

How do I return null if a CAST fails?

2 Answers2