Checking for a particular string format in Oracle 11g

Question

In Oracle 11g, I need to check perform a check to see if the following format is true (i.e. via a regular expression):

PaaaaE0%
PaaaaD2%

where this value is of the following format:

P ( followed by any 4 alphanumeric digits) E or D (followed by atleast 1 numeric digit)

As part of the 4 alphanumeric digits, if alpha then they need to be uppercase.

For now, I have tried something like:

REGEXP_LIKE('PWOOOE12s3','[P][:alnum:]{4}[ED][:digit:]{1}')

Pls see updated question but unsure if correct and unsure how to ensure uppercase. — tonyf
– tonyf, Commented Apr 2, 2014 at 4:21

user272735 · Accepted Answer · 2014-04-02 05:28:00Z

1

You're pretty close:

Matching a charter class should be written as [[:digit:]]. Note the outer braces for a matching list.
{n} matches exactly n occurences, use + to match one or more occurences.
[:upper:] matches uppercase letters, matching list [[:upper:]|[:digit:]] matches any uppercase letter or digit.
I also added ^ anchor to match only from the beginning of the string. You can left it out if it doesn't fit your purpose.

This should get the job done:

SQL@xe> !cat so40.sql
with
data_ as (
  select 1 id, 'PWOOOE12s3' str from dual union
  select 2 id, 'PwoooE12s3' str from dual
)
select id, str from data_
where regexp_like(str, '^P[[:upper:]|[:digit:]]{4}[ED][[:digit:]]+')
;
SQL@xe> @so40

        ID STR
---------- ----------
         1 PWOOOE12s3

SQL@xe>

edited Apr 2, 2014 at 5:28

answered Apr 2, 2014 at 5:06

user272735

10.6k9 gold badges72 silver badges100 bronze badges

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

Collectives™ on Stack Overflow

Checking for a particular string format in Oracle 11g

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related