Home » SQL & PL/SQL » SQL & PL/SQL » to_number problem (ora ver 8i)
to_number problem [message #380146] Fri, 09 January 2009 05:17 Go to next message
gtriant
Messages: 41
Registered: September 2006
Member
Hi all,

I would like to use to_number on a varchar2 column that also includes non number values, in order to get only the number values out of the column.

Possible????
Re: to_number problem [message #380148 is a reply to message #380146] Fri, 09 January 2009 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not possible with built-in functions, create your own one, search for "isnumber" or the like.

Regards
Michel
Re: to_number problem [message #380152 is a reply to message #380146] Fri, 09 January 2009 05:45 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's an example; perhaps you'll be able to use it while developing your own solution. PL/SQL piece of code will display only numeric values. Exception handler (WHEN OTHERS is here on purpose; if it isn't a number, I don't really care what it might be as I want numbers only) will skip "invalid" values.
SQL> create table test_1 (col varchar2(20));

Table created.

SQL> insert all
  2    into test_1 values ('1234')
  3    into test_1 values ('13.24')
  4    into test_1 values ('ab4342')
  5    into test_1 values ('22c')
  6  select * from dual;

4 rows created.

SQL> declare
  2    l_is_number number;
  3  begin
  4    for cur_r in (select col from test_1) loop
  5      begin
  6        l_is_number := to_number(cur_r.col);
  7        dbms_output.put_line(l_is_number);
  8      exception
  9        when others then
 10          null;
 11      end;
 12    end loop;
 13  end;
 14  /
1234
13.24

PL/SQL procedure successfully completed.

SQL>
Re: to_number problem [message #380154 is a reply to message #380146] Fri, 09 January 2009 05:50 Go to previous message
gtriant
Messages: 41
Registered: September 2006
Member
Many thanks!!
Previous Topic: Looping in SQL - Hirerarchical Tree
Next Topic: Multiple Column Selection from Table in a Function
Goto Forum:
  


Current Time: Tue Dec 06 13:56:02 CST 2016

Total time taken to generate the page: 0.06201 seconds