The Oracle database allows you to use regular expressions in SQL queries with the REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE functions. These functions work like the LIKE, INSTR, SUBSTR, and REPLACE functions but accept the regular expression metacharacters (see Resources for a complete list of metacharacters). You can use regular expressions to validate data, find duplicate words, detect extra white space, or parse strings. Regular Expressions can only be used in Oracle 10g or later.
Things You'll Need
- Oracle Database 10g
Open a terminal window (Linux/Unix) or command prompt window (Windows) to access a command prompt where you will type the following commands.
Type the command "sqlplus" to start the SQL*Plus program. You will be presented with a "SQL>" prompt.
Use the REGEXP_LIKE command to find all the rows matching a regular expression. For example to find all the zipcodes in the table address containing a non numeric character, the syntax would be:
SELECT zipcode FROM address WHERE REGEXP_LIKE(zipcode, '[^[:digit:]]'
Type "quit" to exit the SQL*Plus program.
Type "exit" to close the terminal window or command prompt window.
Tips & Warnings
- You can also use regular expressions in the REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE functions.
How to Replace a Substring in Oracle SQL
Oracle allows a portion of a string, called a sub-string, to be easily replaced using structured query language (SQL) functions. The "replace"...
How to Use the Like Operator in SQL Express
SQL Express is a free version of Microsoft's larger-scale SQL Server database package and is used for small applications. Like most Microsoft-developed...
How to Use Regular Expressions to Block Websites
Regular expressions use special characters, or metacharacters, to describe possible combinations of text in strings. For example, you could create a regular...