Select Items That Are NOT numeric

[cfcProgrammer]

Hi,

Please be patient... I'm very new to SQL and Oracle...

I simply want to extract data from a field that is not numberic... The field is defined as varchar2 but I need to clean up the data to exclude the character values in this field. If the field contained only a # or $ in the fields I could just use a LIKE statement but the values could be just about anything.... How can I accomplish this with a simple SELECT statement?



Thank You

 

[BJCooperIT]

Can you create a database function? If you can, this is a simple solution.

function F_IS_NUMERIC_YN (P_STRING in varchar2) 
return varchar2
is
    V_AMT   number;
begin
    --
    --  Move to a numeric field and see if an error is raised
    --
    V_AMT := to_number(P_STRING);
    return('Y');
    --
exception
    when others then return('N');
end F_IS_NUMERIC_YN;

Then you can code your select something like:

select * 
from   My_Table 
where  F_IS_NUMERIC_YN(My_Column) = 'N'

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[taupirho]

The SQL function TRANSLATE was made for this:-

e.g

1 select translate('1AA$$.PP2345XXS6',' '||translate('1AA$$.PP2345XXS6','0123456789',' '),'
')
2* from dual
SQL> /

TRANSL
------
123456


1 select translate('NONUM',' '||translate('NONUM','0123456789',' '),' ')
2* from dual
SQL> /

T
-

 

[BJCooperIT]

While translate works, I am a fan of code that is self documenting and does not leave the next guy scratching his head wondering what it does. There have been many discussions on the Oracle boards regarding this. See thread186-494811.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[taupirho]

Two points,

1) I think I read the question wrong. I thought the OP wanted to extract non-numeric data from his field to leave only the numeric. Now I think they meant the opposite - extract numeric data leaving just the non-numeric. Perhaps they'll clarify.

2) BJ - I like self-documenting code too, the only problem is that your posting (mine too possibly) doesn't answer the OP's question - 'I simply want to extract data from a field ... ' There is no extraction of characters either numeric or otherwise in your "solution

 

[BJCooperIT]

I went with the title: "Select Items That Are NOT numeric

 

[cfcProgrammer]

Thank you for your replies.

Let me clarify...

I have to do a clean up on a table. The account numbers contain character values BUT we only want numeric values in this field. The first step in the clean up is to identify how many records we are looking at. I simply just need to select the records that contain both numeric and character data within the one record.

For example: "Account # 123" I want on my list BUT 897654 I do NOT. I know I can extract this data by using a bunch of LIKE statements BUT there could be just about anything in this field and I thought there must be an faster way.

 

[SantaMufasa]

cfc,

If you wish to determine if an expression is numeric or not, then Barb's (BJ's) solution is what you want. If you want to return just the numeric portion of an expression, then there is a simplified user-defined function that you can use.

If you want such a funtion, then we first need to understand how you want the function to deal with the following expression: "$22 paid $.30 unpaid". Here are the alternative methods of dealing with such an expression:

1) Return "22.30", which results by stripping out all non-numeric characters and returning the remaining characters.

2) Return "22" since it is the first discrete numeric expression.

3) Return NULL since the string is an ambiguous/undetermined numeric expression.

Let us know how you want to deal with these (and any other)issues, then we can help you build a user-defined function to return "cleaned-up", strictly numeric results from your original, incoming expressions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[cfcProgrammer]

Sorry it has taken me so long to get back to you. I've been out of the office for a while.

I used Taupirho's solution(Translate) with the selection and the data clean up.

Thank you to everyone who responded, your help was greatly appreciated.

cfc