Skip to Main Content
  • Questions

  • REGEXP_LIKE - Pattern match with complex logic

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, CHANDRU.

Asked: February 19, 2019 - 7:19 am UTC

Last updated: February 20, 2019 - 2:05 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

I want to do the regexpr pattern matching and I couldn't. Please help.

I want to evaluate the value that is going to be inserted in DB. I want to perform a check as below.

Only four special characters and alphabets are allowed. other special characters are not allowed.

Four characters are . dot, - Hyphen ' apostrophe and space

My value will have meet below condition.

It contains only alphabets.
It contains combination of alphabets and above special characters.
it contains only all spaces.
Except for spaces it should not be simply special characters as ......, ----------, ''''''''.
For example these are valid:

ORACLE
ORA..C L--E
''..O
O--...'''
and these are invalid:

........
''''''''''
--------
ORACLE$
ORACL#E
ORACLE(



I have written code as below to meet the above criteria. But it fails in one condition. Please advise.

Code:
select 'VALID' AS RESULT from dual
where REGEXP_LIKE ('OrAcLe()', '^([[:space:]]+|[A-Za-z''-.[:space:]]*[A-Za-z[:space:]][A-Za-z''-.[:space:]]+)$');

But it allows below 4 invalid characters in data.
open bracket(()
close bracket())
comma (,)
Asterisk (*)

Please let me know why it is allowing these 4 special characters and let me know how to evaluate this.

and Chris said...

Just look for strings that only contain:

- Alpha-numeric characters
- Space
- Apostrophe
- dot
- hyphen

And include at least one alpha-numeric character:

with rws as (
  select 'ORACLE' s from dual union all
  select 'ORA..C L--E' s from dual union all
  select '''..O' s from dual union all
  select q'|'O--...''''|' s from dual union all
  select '........' s from dual union all
  select q'|''''''''''''|' s from dual union all
  select '--------' s from dual union all
  select 'ORACLE$' s from dual union all
  select 'ORACL#E' s from dual union all
  select 'ORACLE(' s from dual
)
  select * from rws
  where  regexp_like ( 
    s , '^[[:alnum:][:space:]''\.\-]+$'
  )
  and    regexp_like ( 
    s , '[[:alnum:]]+'
  );

S             
ORACLE        
ORA..C L--E   
'..O          
'O--...''''

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

CHANDRU S, February 19, 2019 - 1:16 pm UTC

Hi Chris Saxon,

It is only alphabets and not alphanumeric. I have changed to [:alpha:]. Thanks for this.

Your code didn't handled two logics. As I mentioned in my question, if the value is all spaces, it should allow.This is missed. Please help to include.

Second one is combination of spaces and the 3 special characters(. - ') without any alphabets should be rejected.Please handle this as well and submit the answer.

Thanks in advance.
Chris Saxon
February 20, 2019 - 2:04 pm UTC

Sorry, misread your question. I believe the review below has the answer you're looking for.

try this

Mason, February 20, 2019 - 3:01 am UTC

with rws as (
...
)
select * from rws
where regexp_like (
s , '^[[:alpha:][:space:]''\.\-]+$'
)
and
( regexp_like (
s , '[[:alpha:]]+'
)
or regexp_like (
s , '[[:space:]]+'
)
);

Chris Saxon
February 20, 2019 - 2:05 pm UTC

Nice work, thanks

regexp

Rajeshwaran, Jeyabal, February 20, 2019 - 6:13 am UTC

Have said this.

It contains only alphabets.
It contains combination of alphabets and above special characters.
it contains only all spaces.
Except for spaces it should not be simply special characters as ......, ----------, ''''''''.

If input starts with alphabets - then should be followed by either one or more alphabets or above said special chars

If input starts with above said special chars - then should be followed by one or more alphabets

If input starts with space, then it can all together have space in it.

Here is the given of inputs with valid and invalid cases.
demo@ORA12C> select 'valid' as x, q'#ORACLE#' as y      from dual union all
  2  select 'valid' as x, q'#ORA..C L--E#' as y from dual union all
  3  select 'valid' as x, q'#''..O#' as y       from dual union all
  4  select 'valid' as x, q'#O--...'''#' as y   from dual union all
  5  select 'valid' as x, q'#         #' as y   from dual union all
  6  select 'In-valid' as x, q'#........#' as y    from dual union all
  7  select 'In-valid' as x, q'#''''''''''#' as y  from dual union all
  8  select 'In-valid' as x, q'#--------#' as y    from dual union all
  9  select 'In-valid' as x, q'#ORACLE$#' as y     from dual union all
 10  select 'In-valid' as x, q'#ORACL#E#' as y     from dual union all
 11  select 'In-valid' as x, q'#ORACLE(#' as y     from dual;

X        Y
-------- -----------
valid    ORACLE
valid    ORA..C L--E
valid    ''..O
valid    O--...'''
valid
In-valid ........
In-valid ''''''''''
In-valid --------
In-valid ORACLE$
In-valid ORACL#E
In-valid ORACLE(

11 rows selected.

Here is the regex that replaces all the valid values into null values.
demo@ORA12C> set null ?
demo@ORA12C> col x1 format a10
demo@ORA12C> select x,y,
  2    regexp_replace( y ,
  3        '(^([a-z]|[A-Z])([a-z]|[A-Z]|\s|''|\.|\-)+)|^(\s|''|\.|\-)+([a-z]|[A-Z])+|^\s+$') x1
  4  from (
  5  select 'valid' as x, q'#ORACLE#' as y      from dual union all
  6  select 'valid' as x, q'#ORA..C L--E#' as y from dual union all
  7  select 'valid' as x, q'#''..O#' as y       from dual union all
  8  select 'valid' as x, q'#O--...'''#' as y   from dual union all
  9  select 'valid' as x, q'#         #' as y   from dual union all
 10  select 'In-valid' as x, q'#........#' as y    from dual union all
 11  select 'In-valid' as x, q'#''''''''''#' as y  from dual union all
 12  select 'In-valid' as x, q'#--------#' as y    from dual union all
 13  select 'In-valid' as x, q'#ORACLE$#' as y     from dual union all
 14  select 'In-valid' as x, q'#ORACL#E#' as y     from dual union all
 15  select 'In-valid' as x, q'#ORACLE(#' as y     from dual
 16      )
 17  /

X        Y           X1
-------- ----------- ----------
valid    ORACLE      ?
valid    ORA..C L--E ?
valid    ''..O       ?
valid    O--...'''   ?
valid                ?
In-valid ........    ........
In-valid ''''''''''  ''''''''''
In-valid --------    --------
In-valid ORACLE$     $
In-valid ORACL#E     #E
In-valid ORACLE(     (

11 rows selected.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.