46 Regular Expressions

1.  Working with the employees table, and using regular expressions, write a query that returns employees whose first names start with a “S” (uppercase) followed by either a “t” (lowercase) or “h” (lowercase).

SELECT * FROM employees

WHERE REGEXP_LIKE(first_name, '^S(t|h)');

^/$ Matches the start-of-line/end-of-line

() Grouping expression, treated as a single sub-expression

| Alternation operator for specifying alternative matches

2. Investigate the LOCATIONS table:

a.  Describe the table.

DESCRIBE locations;

SELECT constraint_name, constraint_type, r_constraint_name FROM user_constraints WHERE table_name = 'LOCATIONS';

SELECT constraint_name, constraint_type, table_name FROM user_constraints    WHERE r_constraint_name = (SELECT constraint_name FROM user_constraints WHERE  table_name = 'LOCATIONS' AND constraint_type = 'P');

b.  Perform a select that returns all rows and all columns of that table.

SELECT * FROM locations;

c.  Write a query using regular expressions that removes the spaces in the street_address column in the LOCATIONS table.

SELECT street_address, REGEXP_REPLACE(street_address, ' ','')   street_address_changed

FROM locations;

Some most frequently used meta characters:

. (dot) Matches any character in the supported character set,except NULL

?  matches zero or one occurence

* matches zero or more occurences

+ Matches one or more occurences

() Grouping expression, treated as a single sub-expression

\ Escape character

| Alternation operator for specifying alternative matches

^/$ Matches the start-of-line/end-of-line

[] Bracket expression for a matching list matching any one of the expressions represented in the list


Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table.

Regular expression functions:REGEXP_INSTR,  REGEXP_SUBSTR, REGEXP_LIKE,  REGEXP_REPLACE,  REGEXP_COUNT