OraFAQ Forum: SQL & PL/SQL » How can i retrieve data

Home » SQL & PL/SQL » SQL & PL/SQL » How can i retrieve data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How can i retrieve data [message #20798]

Thu, 20 June 2002 20:47

Laxman Chepuri
Messages: 8
Registered: May 2002

Junior Member

i have one table 'x' in which empid is a column declared as varchar2(10)
data in that field is
1
2
3
4
a
5
c
6
d

Now i want to retrieve only numbers
my required output is
1
2
3
4
5
6

Report message to a moderator

Re: How can i retrieve data [message #20800 is a reply to message #20798]

Fri, 21 June 2002 01:04

Anisha
Messages: 1
Registered: June 2002

Junior Member

select * from x where ascii(empid) not between 65 and 112;

Report message to a moderator

Re: How can i retrieve data [message #20806 is a reply to message #20798]

Fri, 21 June 2002 09:11

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

No need for a user-defined function in this case, just use the built-in translate function:

sql>select * from x;
 
EMPID
----------
1
2
3
4
a
5
c
6
d
10
abc
abc10
10abc
 
13 rows selected.
 
sql>select * from x where translate(empid, '0123456789', ' ') is null;
 
EMPID
----------
1
2
3
4
5
6
 
6 rows selected.

Report message to a moderator

Re: How can i retrieve data [message #20808 is a reply to message #20806]

Fri, 21 June 2002 09:18

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Actually, there should be a space before the list of numbers:

translate(empid, ' 0123456789', ' ')

Report message to a moderator

Previous Topic:	change inittrans value for a table
Next Topic:	how to create a job which runs at every 4 minutes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 23:16:30 CDT 2024