Regular Expressions in Oracle 10g - Part 1

Before my current role I generally worked in a windows environment, I would occasionally copy a file or log on to a Unix box to does something only when absolutely necessary. But at my last job and this current one I have had to spend most of my time connected to a Unix server, for as long as I could I'd edit in Windows and copy across to do what I needed but it soon became apparent that I'd need to get with the times ;) and take the plunge into Unix.

I have found Unix to be really powerful and it didn't take anywhere near as long to get used to vi as I feared. One of the things I have most useful is regular expression, now I know that they are available in Windows in various places but in all my time with computers I had only a minimal amount of exposure to them.

This then leads on to today's blog, In Oracle 10g a number of new functions were introduced that allow you to search and update using regular expressions. Over the next few days I'll be looking at what can be done but lets start with some simple examples. Let's take the following data set.

SQL> select * from reg_test;

VAL_1
------------------------------
My name is Andrew
My name is Dave
My name is dave
His name is dave
His name is dave.
He dave me andrew

Before 10g we had to use like to search on sections of a column, this wasn't necessarily a problem but often involved using other functions to format the data in a way that we could search on. For example lets say we wanted to get a count of all of the records with Dave at the end, including those which were upper or lower case. Using like we would do something like this.

SQL> select count(*) from reg_test 
     where upper(val_1) like ('%DAVE');

  COUNT(*)
----------
         3

That's fine but it requires us to covert the whole of the val_1 to upper case so we can pick out the required fields. Also we have a count of 3 when realistically speaking we have 4, one of the rows has a full stop. We could to this..

SQL> select count(*) from reg_test 
     where upper(val_1) like ('%DAVE%');

  COUNT(*)
----------
         5

Now we are getting 5, because we have added the second %. To get round this in 10g we can now use REGEXP_LIKE, this allows us to use regular expressions as the comparison.

I'll assume some knowledge of regular expressions (given you possibly know more about them than I do).

SQL> select count(*) from reg_test 
     where regexp_like(val_1,'[Dd]ave\.*$');

  COUNT(*)
----------
         4

This time we get the right result.