CODEWITHSUNDEEP
regexplsqloracle11g
tonyf
tonyf

Reputation: 35557

Checking for a particular string format in Oracle 11g

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}')

Upvotes: 1

Views: 2879

Answers (1)

user272735
user272735

Reputation: 10648

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>

Upvotes: 1

Related Questions