Home » SQL & PL/SQL » SQL & PL/SQL » modify sql to select only non-alphabetic emplid's (oracle sql)
modify sql to select only non-alphabetic emplid's [message #359070] Thu, 13 November 2008 11:47 Go to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
Hello Folks,

I am attempting to use the following select to get a specific emplid. However, the ps_names table contains some alphabetic characters. I want to only focus on the emplid's that contains numbers. Is there a way to modify the following select to do this?

Thanks,
orayoh

"ORA-01722: invalid number"

SELECT x.y
from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y
from PS_NAMES
where emplid > '000000000' and emplid < '999999999') x
where rownum = 1;
Re: modify sql to select only non-alphabetic emplid's [message #359071 is a reply to message #359070] Thu, 13 November 2008 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "isnumber" or the like.
This has been asked and answered many times.

Regards
Michel
Re: modify sql to select only non-alphabetic emplid's [message #359083 is a reply to message #359071] Thu, 13 November 2008 12:41 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
Michel Cadot wrote on Thu, 13 November 2008 12:52
Search for "isnumber" or the like.
This has been asked and answered many times.

Regards
Michel



Hi Michel,

Yes, I know, but I am working with an emplid field that is of type char. My select is erring on the toNumber function, because my table contains some emplid's in the form A00023498 etc.

I'll give it some more thought.

Thanks,
bubba
Re: modify sql to select only non-alphabetic emplid's [message #359086 is a reply to message #359083] Thu, 13 November 2008 12:51 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
orayoh wrote on Thu, 13 November 2008 13:41

My select is erring on the toNumber


isnumber, not the Oracle function TO_NUMBER.
Re: modify sql to select only non-alphabetic emplid's [message #359089 is a reply to message #359070] Thu, 13 November 2008 12:55 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
joicejohn wrote on Thu, 13 November 2008 13:42
[*** Message deleted. Please ignore this post]


Hmmm...so how would I use it to select only the emplids that do not have an alphabet in them?

For example, I have a list of emplids. If I want to choose the bottom 20 percent of this list of emplids, but first must select only the ones that do not contain letters.

emplid(type = char)
252697441
000325425
000125458
000123456
000124536
000153698
125336877
A00012235
E02458235
A25365845

This is my current sql statement, but it cannot handle the emplids in this form because it contains emplids with letters.

SELECT x.y
from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y
from PS_NAMES
where emplid > '000000000' and emplid < '999999999') x
where rownum = 1;
Re: modify sql to select only non-alphabetic emplid's [message #359092 is a reply to message #359089] Thu, 13 November 2008 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And apart from repeating your question, what did you search since your last post?

Regards
Michel

[Updated on: Thu, 13 November 2008 13:02]

Report message to a moderator

Re: modify sql to select only non-alphabetic emplid's [message #359101 is a reply to message #359089] Thu, 13 November 2008 13:29 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member


orayoh wrote on Fri, 14 November 2008 00:25
joicejohn wrote on Thu, 13 November 2008 13:42
[*** Message deleted. Please ignore this post]




(Sorry I was had a small doubt in my answer. Thats why I deleted my post. Anyways I was correct and I will repost my post)

You can achieve this using REGEXP_LIKE Conditional Function also if you are using Oracle 10g or above. Try to go through the link and try some queries. Come back to us if you are facing any problems with the queries.

Also please read OraFAQ Forum Guide especially on "How to Format Your Post?"

Hope this helps.

regards,
Jo
Re: modify sql to select only non-alphabetic emplid's [message #359104 is a reply to message #359089] Thu, 13 November 2008 13:47 Go to previous messageGo to next message
rishg
Messages: 9
Registered: March 2008
Junior Member
The way we generally look for only numeric or char strings is using the translate or replace function
SELECT x.y
from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y
from PS_NAMES
where translate(emplid, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', '@@@@@@@@@@@@@@@@@@@@@@@@@@###########') = '##########'--(the num of characters here should match the length of your emplid)
where rownum = 1;
Re: modify sql to select only non-alphabetic emplid's [message #359114 is a reply to message #359104] Thu, 13 November 2008 14:55 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
rishg wrote on Thu, 13 November 2008 14:47
The way we generally look for only numeric or char strings is using the translate or replace function
SELECT x.y
from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y
from PS_NAMES
where translate(emplid, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', '@@@@@@@@@@@@@@@@@@@@@@@@@@###########') = '##########'--(the num of characters here should match the length of your emplid)
where rownum = 1;


Thanks rishg,
orayoh
Re: modify sql to select only non-alphabetic emplid's [message #359115 is a reply to message #359114] Thu, 13 November 2008 14:56 Go to previous message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
orayoh wrote on Thu, 13 November 2008 15:55
rishg wrote on Thu, 13 November 2008 14:47
The way we generally look for only numeric or char strings is using the translate or replace function
SELECT x.y
from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y
from PS_NAMES
where translate(emplid, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', '@@@@@@@@@@@@@@@@@@@@@@@@@@###########') = '##########'--(the num of characters here should match the length of your emplid)
where rownum = 1;


Thanks rishg,
orayoh


Thanks all.
Previous Topic: Regular Expression Query (REGEXP_SUBSTR)
Next Topic: doing a select on multiple databases in a single query
Goto Forum:
  


Current Time: Wed Dec 07 08:49:55 CST 2016

Total time taken to generate the page: 0.13352 seconds