• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 890
  • Last Modified:

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

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
klyles95
Asked:
klyles95
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Try:
Regexp_like(last_name,'([0-9]+[A-Za-z]+[a-zA-Z0-9]*)|([a-zA-Z]+[0-9]+[a-zA-Z0-9]*)
0
 
sdstuberCommented:
same idea, slightly simplified

Regexp_like(last_name,'([0-9]+[A-Za-z]+|[a-zA-Z]+[0-9]+)[a-zA-Z0-9]*')
0
 
klyles95Author Commented:
Thank you slightwv.  Worked like a charm
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sdstuberCommented:
already closed but even simpler...


Regexp_like(last_name,'([0-9]+[A-Za-z]|[a-zA-Z]+[0-9])[a-zA-Z0-9]*')
0
 
klyles95Author Commented:
Thank you sdstuber.  This works too!
0
 
sdstuberCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now