Avatar of d27m11y d27m11y
d27m11y d27m11y
Flag 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
Oracle Database

Avatar of undefined
Last Comment
d27m11y d27m11y

8/22/2022 - Mon
pdd1lan

ASKER CERTIFIED SOLUTION
choukssa

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
choukssa


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.
slightwv (䄆 Netminder)

>>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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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
slightwv (䄆 Netminder)

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.
d27m11y d27m11y

ASKER
Thanks for the help and it helped me resolve my issue
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.