Home » SQL & PL/SQL » SQL & PL/SQL » How to select numeric values from a column
How to select numeric values from a column [message #356626] Fri, 31 October 2008 10:02 Go to next message
kavi123
Messages: 13
Registered: January 2007
Junior Member
Hi All,

I had a situation where I need to select only numeric values from a column which includes both numeric and alphanumeric values.Also there is no specific starting value for the numeric values.How to provide condition for this case?


Thanks,
Have a Nice Day.
Re: How to select numeric values from a column [message #356632 is a reply to message #356626] Fri, 31 October 2008 10:30 Go to previous messageGo to next message
fairgame
Messages: 29
Registered: October 2008
Junior Member
Like this...
SQL> create table t2 (name varchar2(10));

Table created.

SQL> insert into t2 values('hello');

1 row created.

SQL> ed
Wrote file afiedt.buf

  1* insert into t2 values('How')
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

  1* insert into t2 values('123')
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

  1* insert into t2 values('10')
SQL> 
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

  1* insert into t2 values('999')
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

  1* insert into t2 values('name')
SQL> /

1 row created.

SQL> select * from t2
  2  ;

NAME                                                                            
----------                                                                      
hello                                                                           
How                                                                             
123                                                                             
10                                                                              
999                                                                             
name                                                                            

6 rows selected.

SQL> select * 
from ( select translate(lower(name), 
                        'abcdefghijklmnopqrstuvwxyz', 
                        '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$') tr from t2) 
where instr(tr,'$') = 0 ;

TR                                                                              
----------                                                                      
123                                                                             
10                                                                              
999                                                                             

SQL> spool off


[Updated on: Fri, 31 October 2008 11:29] by Moderator

Report message to a moderator

Re: How to select numeric values from a column [message #356634 is a reply to message #356626] Fri, 31 October 2008 10:34 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
For 10g+ you can use:

select * from <table_name>
where regexp_like(<column_name>, '^[0-9]+$');

For all versions you can use:

select * from <table_name>
where translate(<column_name>, ' 0123456789', ' ') is null;


Re: How to select numeric values from a column [message #356635 is a reply to message #356634] Fri, 31 October 2008 10:36 Go to previous messageGo to next message
fairgame
Messages: 29
Registered: October 2008
Junior Member
Better way if your numbers have Decimal and Engg values too..
combining PL/SQL
create or replace
function is_number (inStr varchar2 )
return integer
IS
n number;
BEGIN
n := to_number(inStr);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;

SQL> select * from test1;

STR
----------------------------------------
AAA
10
1.1
1.2.2
1/2
.5
1e5
-10

8 rows selected.

SQL> select *
2 from test1
3 where decode( 
4 replace(translate(str,'1234567890.-e','0000000000000'),'0',''),
5 NULL, is_number(str),
6 0 ) = 1;

STR
----------------------------------------
10
1.1
.5
1e5
-10


[Updated on: Fri, 31 October 2008 10:39]

Report message to a moderator

Re: How to select numeric values from a column [message #356641 is a reply to message #356626] Fri, 31 October 2008 12:25 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
We haven't heard back from the OP, but I read this question as columns can contain mixed values, such as AS/400, in which case, I don't think any of these work. But that was just how I read it.
Previous Topic: Dynamic Cursor in Procedure
Next Topic: EXECUTE IMMEDIATE ; ROWTYPE as USING
Goto Forum:
  


Current Time: Sat Feb 15 18:03:15 CST 2025