Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using oracle's Regexp to return fields that are alphanumeric only

Posted on 2011-03-19
6
Medium Priority
?
888 Views
Last Modified: 2012-05-11
Hello,

I am having a poblem using regexp to return rows where the searched column has alphanumeric data.  It must contain a mix of characters and numbers and NOT only alpha characters and NOT only digits. So for example, a table with the column last_name contains

LAST_NAME
Brown
Ja2k
1Long
12345

I want the search to return rows with Ja2k and 1Long because it has a mix of digits and alphas in any occurance.  All alphas and all digits are rejected

The following does not work as required:

select * from tablea
where regexp_like(last_name, '^[[:alnum:]].+[[:alpha:]]*$' )
0
Comment
Question by:klyles95
  • 3
  • 2
6 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 35173150
Try:
Regexp_like(last_name,'([0-9]+[A-Za-z]+[a-zA-Z0-9]*)|([a-zA-Z]+[0-9]+[a-zA-Z0-9]*)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35173503
same idea, slightly simplified

Regexp_like(last_name,'([0-9]+[A-Za-z]+|[a-zA-Z]+[0-9]+)[a-zA-Z0-9]*')
0
 

Author Closing Comment

by:klyles95
ID: 35173857
Thank you slightwv.  Worked like a charm
0
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.

 
LVL 74

Expert Comment

by:sdstuber
ID: 35173967
already closed but even simpler...


Regexp_like(last_name,'([0-9]+[A-Za-z]|[a-zA-Z]+[0-9])[a-zA-Z0-9]*')
0
 

Author Comment

by:klyles95
ID: 35182228
Thank you sdstuber.  This works too!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35182593
actually,  all of the above are incorrect

they will all give a false positive for strings like this...

'12a!bc~34*xyz'

try this instead...

REGEXP_LIKE(last_name, '^([0-9]+[A-Za-z]|[a-zA-Z]+[0-9])[a-zA-Z0-9]*$')


you could add ^ and $ to the others as well
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This video shows how to recover a database from a user managed backup
Suggested Courses

972 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