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