In 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).

Enjoy