Oracle SQL REGEX_LIKE

Question

SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

The following query returns the first and last names for those employees with a first name of Steven or Stephen (where first_name begins with Ste and ends with en and in between is either v or ph)

is there a call that is opposite where the query will return everything that would not have (v or ph) between Ste and en?

so that it would return things like:
Stezen
Stellen

is it as simple as putting NOT in front of REGEXP_LIKE?

Answer 1

How about MINUS

SELECT *
FROM employees
WHERE REGEXP_LIKE( first_name , '^Ste([[:alpha:]])+en$')
MINUS
SELECT *
FROM employees
WHERE REGEXP_LIKE( first_name , '^Ste(v|ph)en$');

and this too:

WITH t AS
     ( SELECT 'Stezen' first_name FROM dual
     UNION ALL
     SELECT 'Steven' FROM dual
     UNION ALL
     SELECT 'Stephen' FROM dual
     )
SELECT *
FROM t
WHERE REGEXP_LIKE( first_name , '^Ste([[:alpha:]])+en$')
 AND NOT REGEXP_LIKE( first_name , '^Ste(v|ph)en$');

Answer 2

You need something like this:

SELECT 'Match'
FROM dual
WHERE REGEXP_LIKE ('Steden', '^Ste[^(v|ph)]en$');

EDIT

This will exclude any two (or more) letter combinations but still allow "v" :

SELECT 'Match'
FROM dual
WHERE REGEXP_LIKE ('Stephen', '^Ste[[:alpha:]]en$');

Since Oracle does not support look-ahead functionality, I will have to agree with others that we will have to deal with "v" explicitly, either by excluding the entire name(word) or at least specifying its exact position.

SELECT name
FROM WhateverTable
WHERE REGEXP_LIKE (name, '^Ste[[:alpha:]]en$') AND SUBSTR(name, 4, 1) <> 'v';