Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.
COURSE of the MONTH
14 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Find a non-numeric character
Posted on 2011-03-04
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
Column A has varchar2(100) as the datatype.
Can someone suggest asap
[X]
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
8 Comments
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
Active 1 day ago
>>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','
FYI:
With that in-list, I can't think of any columns possible that would be non-numeric.
Wouldn't it be easier to do: REGEXP_LIKE (col1, '[^0-9]+')
>>and postal_code in ('00501','00501','00544','
FYI:
With that in-list, I can't think of any columns possible that would be non-numeric.
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.
Active 1 day ago
>>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.
Cool. Just making sure. In my test cases I prefer to show both sides. Bad rows as well as good rows.
I tried this for my business scenario. We were expecting the columns are filled with '(' or ')' or '-'
select
pid,phonenumber,contacttyp
from
memberphone mp
,lookup l
WHERE
REGEXP_LIKE(phonenumber, '()-')
and l.lookupid = mp.contacttypeid
Active 1 day ago
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.
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.
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Working with Network Access Control Lists in Oracle 11g (part 2)
Part 1: http://www.e-e.com/A_8429.html
Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access. For many…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux.
Script may need to be modified depending on OS-installation.
Please deploy and verify the script in a test environment.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month14 days, 17 hours left to enroll
770 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.