How to select numeric values from a column [message #356626] |
Fri, 31 October 2008 10:02  |
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   |
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 #356635 is a reply to message #356634] |
Fri, 31 October 2008 10:36   |
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
|
|
|
|