Tuesday, May 9, 2017

Fascinating Things about Regular Expressions and Oracle

I'm finally doing some programming in my role as a programmer at work. I'm a "Support Programmer" which means my bread and butter is correcting the data the "Programmer programmers" didn't get right the first time. Occasionally, I get to modify code, and recently I wrote something that added some functionality to a piece of existing code. To help validate my change, I had to create a query that would parse a log message, and aggregate some data so I could understand what got processed by the code I modified.
I learned the following important facts:

  • REGEXP_SUBSTR is a great Oracle function. There are some quirks with it that need to be considered, but all in all, I'm trying to always use it instead of SUBSTR. One of the limitations in REGEXP_SUBSTR is that for now, there's no support for look behind or look ahead functions. To work around this, I had to take a REGEXP_SUBSTR of my REGEXP_SUBSTR.
  • Pattern matching requires its own mindset. You must not only keep the end result of the pattern you want in your mind, but you must also consider what the pattern is on the way to becoming what you want.
  • TIMTOWTDI is a blessing and a curse. There may be more than one way to do it, but there can also be more than one way to almost do it. This can be dangerous.
As I continue to grow and develop as a programmer, I chase after "The Better Way". Because SQL has a well structured set of instructions, that better way is often subtle, and involves understanding things like profile optimizers, and indexed searches. Every day I respect, and like SQL more.

Post a Comment