asked on

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:]]*$' )

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

same idea, slightly simplified

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

klyles95

ASKER

Thank you slightwv. Worked like a charm

Sean Stuber

already closed but even simpler...

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

klyles95

ASKER

Thank you sdstuber. This works too!

Sean Stuber

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