Advanced SQL

• Regular expressions can provide more precise filtering in a WHERE clause
• They are a pattern language for searching text
• Use regular expressions for tasks such as:
  • Finding, filtering or replacing phone numbers in a text file
  • Finding files with digits in the middle of their names
  • Finding repeated words in a text file
  • Finding and replacing patterns in text
• Regular expressions are supported by many tools, including:
  • Operating systems
  • Text editors
  • File-renaming tools
  • SQL clients
• Regular expressions have their own syntax

To learn more about regular expressions, see "Learning Regular Expressions" by Ben Forta

• Oracle provides four functions for working with regular expressions
  • REGEXP_LIKE( )
  • REGEXP_REPLACE( )
  • REGEXP_INSTR( )
  • REGEXP_SUBSTR( )
• The examples below use regular expressions only for filtering in WHERE
• Use REGEXP_LIKE( ) to filter in a WHERE clause

-- Oracle	
SELECT prod_name
FROM   Products
WHERE  REGEXP_LIKE(prod_name, '.2');

PROD_NAME

12 inch teddy bear

The example above can also be written with LIKE, but would require several wildcards.

• A dot in a regular expression matches any single character
• Oracle regular-expression matching is case-sensitive by default
• Use the pipe character (|) for an OR alternative

-- Oracle	
SELECT prod_name
FROM   Products
WHERE  REGEXP_LIKE(prod_name, '8 | 12 | 18');

PROD_NAME

8 inch teddy bear

18 inch teddy bear

• As well as a dot for any character, use brackets [ ] to specify a set of characters
• A character set in brackets is another way to express alternatives

-- Oracle	
SELECT prod_name
FROM   Products
WHERE  REGEXP_LIKE(prod_name, '[128] inch');

PROD_NAME

8 inch teddy bear

12 inch teddy bear

18 inch teddy bear

The expression above is a shorter way to specify the alternatives 1, 2 or 8

• Put ^ at the start of a character set to negate it

-- Oracle	
SELECT prod_name
FROM   Products
WHERE  REGEXP_LIKE(prod_name, '[^12] inch');

• Character sets [ ] can be used to match different characters
• [1234567890] matches a digit. It can be shortened to [0-9]
• Ranges such as [6-9] and [a-m] are also valid

-- Oracle	
SELECT prod_name
FROM   Products
WHERE  REGEXP_LIKE(prod_name, '[1-8] inch');

• Regular expressions use special characters such as . | [ ] ^ and -
• To match one of these characters literally, use the escape character \
• Place the backslash before the special character

SELECT cust_contact
FROM   Customers
WHERE  REGEXP_LIKE(cust_contact, '\.');	

• Character sets are very common in regular expressions
• Predefined sets make frequently used character groups easier to specify
• Predefined character sets are called character classes