regular expression to allow only alphanumeric characters

Question

I want my regular expression should error out if my string contains any character other than Alphanumeric. The string should error out if there is a new line character or any white space character in between the string or at the end or at the beginning. I am using below code -

SELECT CASE WHEN NOT REGEXP_LIKE(MYSTRING, '^[a-zA-Z0-9]*$') THEN 'invalid'
       ELSE 'valid'
       END 
FROM DUAL

this works when

MYSTRING in ('ABCD ',' ABCD', 'ab cd','ab+cd')

But it does not work when there is new line char at the end of the string

MYSTRING  = 'ABCD'||chr(10);

I want it should error out when there is any char other than alphanumeric. Please advise. Thanks in advance.

I understand you do not want to match it, so use '\A[a-zA-Z0-9]*\z' — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 23 at 23:57
Glad it worked, I added the answer below. Note it is a common PCRE-like regex thing, although in some regex flavors, like Python's re, \z is not supported, \Z is used instead, BUT in PCRE regex flavor, \Z matches the end of text but also any position before trailing newline(s). So, we are lucky here that this flavor supports the PCRE construct. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 17 at 9:58

Wiktor Stribiżew · Accepted Answer · 2025-05-26 10:54:25Z

3

You are using $ anchor, that is known to match a position before the final newline character in the string.

In this case, you just need to use the very end of the string with \z (you may also match the very start of the string with \A):

REGEXP_LIKE(MYSTRING, '\A[a-zA-Z0-9]*\z')

See this Oracle reference:

Operator	Description
\A	Match only at beginning of string
\z	Match only at end of string

SQL Test:

SELECT CASE WHEN REGEXP_LIKE('ABCD'||chr(10), '\A[a-zA-Z0-9]*\z') THEN 'valid'
       ELSE 'invalid'
       END 
FROM DUAL

Result:

edited May 26 at 10:54

answered May 24 at 0:06

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sln · Accepted Answer · 2025-05-24 17:56:56Z

2

Not sure if the sql regex engine supports assertions, but if so.
Apparently assertions aren't included. Please disregard this answer.

Just tested it at https://livesql.oracle.com/next/.
Adding a \z at the end solves the issue.

WHEN REGEXP_LIKE('Abc123' , '^[a-zA-Z0-9]+\z')

edited May 24 at 17:56

answered May 24 at 16:57

sln

3,6431 gold badge7 silver badges13 bronze badges

Collectives™ on Stack Overflow

regular expression to allow only alphanumeric characters

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related