Solved

Find a non-numeric character

Posted on 2011-03-04
8
633 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:d27m11y
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 8

Expert Comment

by:pdd1lan
ID: 35040240
0
 
LVL 2

Accepted Solution

by:
choukssa earned 125 total points
ID: 35040715

 I have a postal_code table that has numeric data stored in varchar2  field. I am using this regular exp  '^[a-zA-Z]*$'  to find if any chars are alpha. You can extend this regex to have special chars also.
SQL>     SELECT postal_code FROM postal_code WHERE REGEXP_LIKE (postal_code, '^[a-zA-Z]*$')
    and postal_code in ('00501','00501','00544','00544','00601','00601','00601','00601','00602');
  2
no rows selected

Open in new window

0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 35041192
>>You can extend this regex to have special chars also.

Wouldn't it be easier to do:  REGEXP_LIKE (col1, '[^0-9]+')


>>and postal_code in ('00501','00501','00544','00544','00601','00601','00601','00601','00602');

FYI:

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

Expert Comment

by:choukssa
ID: 35041287

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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35041301
>>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.
0
 

Author Comment

by:d27m11y
ID: 35058171


 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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35058342
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.
0
 

Author Closing Comment

by:d27m11y
ID: 35058478
Thanks for the help and it helped me resolve my issue
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now