Regular expressions in Oracle SQL

Here are some examples of using regular expressions in Oracle SQL along with the resulting output:

1. Using REGEXP_LIKE to find all rows in a table where the email column contains a valid email address format:

SELECT * FROM my_table WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

1. Using REGEXP_REPLACE to replace all occurrences of the word "apple" with "orange" in a string column:

SELECT REGEXP_REPLACE(column_name, 'apple', 'orange') AS new_column_name FROM my_table;

Output:

3. Using REGEXP_INSTR to find the position of the first occurrence of a word in a string column:

SELECT REGEXP_INSTR(column_name, 'word') AS position

FROM my_table;

Output: