Express Yourself – String Comparison in Oracle using REGEXP_REPLACE

As a programmer, one of the absolute pleasures of Unix ( especially when compared to Windows) is the flexibility offerred by the use of Regular Expressions. Not only do tools such as Sed and Awk offer almost unlimited options for manipulating text files, but Regular Expression syntax offers an unparalleled opportunity to exercise the top row of your keyboard.
Regular Expressions have been available in Oracle for a while now, but it’s one of those incredibly useful features that doesn’t seem to be widely used.
This is no doubt due, in part, to the fairly involved syntax, but help is at hand. There’s a particularly good explaination of most of the Oracle Regular Expression functions ( and syntax) on the excellent PSOUG reference site ( formerly known as Morgan’s Library).

I want to concentrate here on the REGEXP_REPLACE function and how you can use it to help when comparing strings in the database that have come from different sources and are, as a result, in different formats. Continue reading