How to Use Regular Expressions in SQL


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.


Promoted By Zergnet



You May Also Like

Related Searches

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!