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.

resultOfRegexp1

 

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

VC1

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.

 

VC2

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

 

VC4

 

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

VC5

 

VC6

 

 

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

EnjoydbaOnTap