Link to home
Start Free TrialLog in
Avatar of d27m11y d27m11y
d27m11y d27m11yFlag for United States of America

asked on

Find a non-numeric character

I want to find if there is any non-numeric character in the column A in table test.

Column A has varchar2(100) as the datatype.

Can someone suggest asap
Avatar of pdd1lan
pdd1lan

ASKER CERTIFIED SOLUTION
Avatar of choukssa
choukssa
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

With that in-list, I can't think of any columns possible that would be non-numeric.

Yes that is the point. That's why it does not return any rows. I was trying to demonstrate that its a varchar2 field with numeric values.
>>Yes that is the point.

Cool.  Just making sure.  In my test cases I prefer to show both sides.  Bad rows as well as good rows.
Avatar of d27m11y d27m11y

ASKER



 I tried this for my business scenario.  We were expecting the columns are filled with '(' or ')'  or '-'

select
pid,phonenumber,contacttypeid,l.lookupvalue  
from
memberphone mp
,lookup l
WHERE
REGEXP_LIKE(phonenumber, '()-')
and l.lookupid = mp.contacttypeid
Finding non-numeric versus finding non-valid phone numbers are two different questions.

Non-numerics can be found with any of the first three posts and answers the question asked.

If you now need to find invalid phone numbers, I suggest you close this question by choosing all or what you consider the best method of findind non-numerics and open a new question and provide more detailed requirements.

You would need to specific ALL the valid masks for a phone number.
Thanks for the help and it helped me resolve my issue