HOW TO prevent TO NUMBER erroring a queryThis is a featured page

Platform: Oracle App:
Tool:
DB Ver: 9.2 App Ver:
Tool Ver:
Revision Date: 21-Feb-2006 Keywords: HOWTO, TO_NUMBER, QUERY

Goal
To prevent TO_NUMBER erroring in a running query?

Facts


Solution
Sometimes we need to query on a table that has a varchar column that needs to be cast to a number. However, if the column can potentially hold characters as well as numbers then TO_NUMBERing the column will throw an exception. This is common in accessing ATTRIBUTE columns in applications that have different context dependent values.

In the Oracle Applications situation, the context (ATTRIBUTE_CONTEXT) should be part of the WHERE clause, but to filter out any old data, for example if the use of the context has changed, an additional filter is required to ensure the TO_NUMBER will work. This can be done by including the following filter in the WHERE clause:

where LTRIM(col1,'0123456789') IS NULL

An example of this working is given below:

SQL> create table test1
2 (col1 varchar2(10))
3 /

Table created.

SQL> insert into test1 values (10);

1 row created.

SQL> insert into test1 values (15);

1 row created.

SQL> insert into test1 values ('fish');

1 row created.

SQL> insert into test1 values ('face');

1 row created.

SQL> insert into test1 values (399);

1 row created.

SQL> select to_number(col1)
2 from test1;
ERROR:
ORA-01722: invalid number


no rows selected

SQL>
SQL> ed
Wrote file afiedt.buf

1 select to_number(col1)
2 from test1
3* where LTRIM(col1,'0123456789') IS NULL
SQL> /

TO_NUMBER(COL1)
---------------
10
15
399



consultium
consultium 		Latest page update: made by consultium , Oct 9 2009, 12:03 PM EDT (about this update About This Update consultium Edited by consultium

93 words added

view changes
- complete history)
Keyword tags: HOWTO QUERY TO_NUMBER
More Info: links to this page

Threads for this page

There are no threads for this page.  Be the first to start a new thread.