REGEXP

REGEXP_LIKE

SELECT * FROM scott.emp WHERE REGEXP_LIKE(ENAME,'FR') ;
OR
SELECT * FROM scott.emp WHERE ENAME LIKE '%FR%'

-------------------

SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^Ste(v|ph)en$');
OR
SELECT testcol FROM test WHERE testcol like 'Steven%' or testcol like 'Stephen%'



parameters can be a combination of (Match Options)
* i: to match case insensitively
* c: to match case sensitively
* n: to make the dot (.) match new lines as well
* m: to make ^ and $ (Anchoring Characters) match beginning and end of a line in a multiline string

If you want all the entries that start with S, search for ^s

End with R, use r$

Start with S and end with H, use ^s.*h$

Start with S or end with R, use ^s|r$

All 4 letter names, use ^….$

Contains B, C, D or K, use [b-d,k]

All names with double letters, use (.)\1

Posix Characters
[:alnum:] Alphanumeric characters
[:alpha:] Alphabetic characters
[:blank:] Blank Space Characters
[:cntrl:] Control characters (nonprinting)
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
[:punct:] Punctuation characters
[:space:] Space characters (nonprinting),such as carriage return,newline,vertical tab, and form feed
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexidecimal characters

Quantifier Characters
* Match 0 or more times
? Match 0 or 1 time
+ Match 1 or more times
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times

Alternative Matching And Grouping Characters
| Separates alternates, often used with grouping operator ()
( ) Groups subexpression into a unit for alternations,for quantifiers,or for back referencing (see "Backreferences" section)
[char] Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - meta characters

SELECT * FROM scott.emp WHERE REGEXP_LIKE(ENAME,'^FR$') ;
or
SELECT * FROM scott.emp WHERE ENAME LIKE 'FR'


SELECT REGEXP_REPLACE('FYICenter.com', '*.com','i') FROM DUAL;
or
SELECT REGEXP_REPLACE ('FYICenter.com', '^*.com$','i') FROM DUAL;



Here we search for strings that have either an "a", "c", or "f" as the second character of the string.
select REGEXP_REPLACE( 'paddy', '^.[acf]' ) from dual

SELECT REGEXP_REPLACE ('FYICenter.com', '^f.*$','i') FROM DUAL;

SELECT REGEXP_REPLACE ('FYICenter.com', '^Y.*$','i') FROM DUAL;

SELECT REGEXP_REPLACE ('FYICenter.com', '*Y.*$','i') FROM DUAL;


SELECT cust_email old_email,REGEXP_REPLACE(cust_email,'@.*\.COM','@BIRDS.COM') new_email FROM oe.customers;


SELECT REGEXP_REPLACE('H1234 H4321 H2345 H2345','(.*) (.*) (.*) (.*)','\4 ,\3, \2, \1')FROM dual;