Once in a while you need to query data in the Oracle database based on a simple question, but creating the query doesn’t seem that simple. Recently I was asked the following question:
Find all strings in a table that have 100 or more characters in sequence without a whitespace character in between that sequence.
A simple question, but how are you going to query this? Maybe do some counting of characters, searching for whitespace characters you first need to define and performing a lot of INSTR and SUBSTR ?
Sounds like a lot of work and a lot of difficult coding. But there is a solution that is a lot easier: regular expressions. Via regular expressions you can search through data using patterns.
When looking at examples of regular expressions it might look daunting af first, but it is basicly a set of conventions you need to learn. After a while you begin to see how powerful regular expressions can be.
Take the example of the 100 character sequence, you can construct a query with minimal coding that answers the question:
select *
from my_table
where regexp_instr(my_column, '\S{100,}') > 0
As I said, minimal coding. regexp_instr is an Oracle function that searches a string based on a regular expression pattern. The regular expression itself is short and consist of two parts:
- \S: matches any character that is not a whitespace character
- {100,}: defines the number of occurrences of the previous pattern, in this case it should occur at least 100 times
Of course there are a lot more possibilities with regular expressions, check out the Oracle documentation on regular expressions and some other examples on Wikipedia.
Whitehorses is specialized in succesfully implementing Oracle SOA solutions: BPEL, OSB, WebLogic & BPM