REGEXP Pattern matching

Question

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 alphabeticals 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

Answer 1

Use the regular expression ^( +|[A-Z '.-]*[A-Z][A-Z '.-]*)$ to match a string consisting of only either:

•  +

  all space characters; or
• [A-Z '.-]*[A-Z][A-Z '.-]*
  zero-or-more alphabetical or special characters then one alphabetical character and then zero-or-more alphabetical or special characters.

Like this:

Oracle Setup:

CREATE TABLE data (
  value VARCHAR2(100),
  CONSTRAINT value__alpha_or_special_chr CHECK ( REGEXP_LIKE( value, '^( +|[A-Z ''.-]*[A-Z][A-Z ''.-]*)$' ) )
);

Insert valid data:

INSERT INTO data ( value )
SELECT q'!ORACLE!'       FROM DUAL UNION ALL
SELECT q'!ORA..C  L--E!' FROM DUAL UNION ALL
SELECT q'!'''..O!'       FROM DUAL UNION ALL
SELECT q'!O--...''''!'   FROM DUAL UNION ALL
SELECT q'!     !'        FROM DUAL

Invalid data fails:

INSERT INTO data ( value )
SELECT q'!''''''''''!' FROM DUAL;

ORA-02290: check constraint (FIDDLE_TRJCYMMSYLSIPALCEYXD.VALUE__ALPHA_OR_SPECIAL_CHR) violated

INSERT INTO data ( value )
SELECT q'!--------!'   FROM DUAL;

ORA-02290: check constraint (FIDDLE_TRJCYMMSYLSIPALCEYXD.VALUE__ALPHA_OR_SPECIAL_CHR) violated

INSERT INTO data ( value )
SELECT q'!ORACLE$!'    FROM DUAL;

ORA-02290: check constraint (FIDDLE_TRJCYMMSYLSIPALCEYXD.VALUE__ALPHA_OR_SPECIAL_CHR) violated

INSERT INTO data ( value )
SELECT q'!ORACL#E!'    FROM DUAL;

ORA-02290: check constraint (FIDDLE_TRJCYMMSYLSIPALCEYXD.VALUE__ALPHA_OR_SPECIAL_CHR) violated

Query:

SELECT * FROM data;

Output:

| VALUE        |
| :----------- |
| ORACLE       |
| ORA..C  L--E |
| '''..O       |
| O--...''''   |
|              |

db<>fiddle here