Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
3 days, 5 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 today
>>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 today
>>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 today
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
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Background
In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself).
I will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month3 days, 5 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
695 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.