[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Find a non-numeric character

Posted on 2011-03-04
Medium Priority
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
Question by:d27m11y
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1

Accepted Solution

choukssa earned 500 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');
no rows selected

Open in new window

LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 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');


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

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Expert Comment

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.
LVL 77

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.

Author Comment

ID: 35058171

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

memberphone mp
,lookup l
REGEXP_LIKE(phonenumber, '()-')
and l.lookupid = mp.contacttypeid
LVL 77

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.

Author Closing Comment

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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

650 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