Database, Oracle, REGEXP_REPLACE, Regular Expressions

Using Virtual Columns with REGEXP

caret1In a previous post, I discussed the REGEXP_REPLACE¬†function. Let’s combine this example with the virtual columns feature introduced in Oracle Database 11g. In that example, we looked at using the REGEXP_REPLACE function in the select statement to display a phone number in the desired format.



A similar construct can be used to define a virtual column on a database table allowing you to select the column with the appropriate format using a simple select statement.

Create a table defining the virtual column. If you want to read up on Virtual Columns, check out this post by my friend ORACLE-BASE


Notice the REGEXP function as part of the virtual column definition. Once we have this, we can insert data into the table. Since the virtual column (PHONE_VIRT) is created via the function referencing the PHONE column, we do not have to insert anything into that column.



After a successful INSERT, let’s query this table and see what we get:




There you have it! Now let’s talk about a few caveats with this.

  • The source column must meet the format criteria required by the REGEXP function in order for the Virtual Column to apply the REGEXP function. Otherwise the output will not change






  • In order to avoid data formatting issues, check constraints on source columns (next post).






This site uses Akismet to reduce spam. Learn how your comment data is processed.