Home » SQL & PL/SQL » SQL & PL/SQL » Problem in number selection
Problem in number selection [message #185774] Thu, 03 August 2006 09:47 Go to next message
deepayan
Messages: 51
Registered: December 2005
Member
Dear all,

I need a solution of a problem. I have a column of type varchar2 which contains varchar2 type data as well as numbers(in varchar2 format).Now I want those rows only having values greater than 50000.It should not consider any character data.It will consider the numbers only & having value greater than 50000.

Thanks in advance.
--deepayan
Re: Problem in number selection [message #185777 is a reply to message #185774] Thu, 03 August 2006 09:53 Go to previous messageGo to next message
anandshetye
Messages: 1
Registered: August 2006
Junior Member
please refer http://asktom.oracle.com/pls/ask/f?p=4950:8:3825101693476404515::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:11504677087008


-anand
Re: Problem in number selection [message #185780 is a reply to message #185774] Thu, 03 August 2006 10:00 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Try this
select * 
 from table1 
where col1 not in (
    select col1 
      from table1 
     where trim(translate(trim(col1),'1234567890',' ' ))<> ' ')
 and col1 >'50000'


Naveen

[Updated on: Thu, 03 August 2006 10:13]

Report message to a moderator

Re: Problem in number selection [message #185878 is a reply to message #185774] Fri, 04 August 2006 00:20 Go to previous message
deepayan
Messages: 51
Registered: December 2005
Member
Thank u.your ideas helped me a lot.
Previous Topic: KUP-04063: unable to open log file EXTERNAL_AUTO1_TABLE_480_3988.log
Next Topic: Pro *c Vs Stored procedures
Goto Forum:
  


Current Time: Thu Dec 08 00:00:23 CST 2016

Total time taken to generate the page: 0.08454 seconds