Is there any such thing as ANSI Standard SQL ?
Lots of databases claim to conform to this standard. Recent experience tends to make me wonder whether it’s more a just basis for negotiation.
This view is partly the result of having to juggle SQL between three different SQL parsers in the Cloudera Hadoop infrastructure, each with their own “quirks”.
It’s worth remembering however, that SQL differs across established Relational Databases as well, as a recent question from Simon (Teradata virtuoso and Luton Town Season Ticket Holder) demonstrates :
Is there an Oracle equivalent of the Teradata LIKE ANY operator when you want to match against a list of patterns, for example :
like any ('%a%', '%b%')
In other words, can you do a string comparison, including wildcards, within a single predicate in Oracle SQL ?
The short answer is yes, but the syntax is a bit different….
The test table
We’ve already established that we’re not comparing apples with apples, but I’m on a bit of a health kick at the moment, so…
create table fruits as select 'apple' as fruit from dual union all select 'banana' from dual union all select 'orange' from dual union all select 'lemon' from dual /
The multiple predicate approach
Traditionally the search statement would look something like :
select fruit from fruits where fruit like '%a%' or fruit like '%b%' / FRUIT ------ apple banana orange
Using REGEXP_LIKE takes a bit less typing and – unusually for a regular expression – less non-alphanumeric characters …
select fruit from fruits where regexp_like(fruit, '(a)|(b)') / FRUIT ------ apple banana orange
We can also search for multiple substrings in the same way :
select fruit from fruits where regexp_like(fruit, '(an)|(on)') / FRUIT ------ banana orange lemon
I know, it doesn’t feel like a proper regular expression unless we’re using the top row of the keyboard.
Alright then, if we just want to get records that start with ‘a’ or ‘b’ :
select fruit from fruits where regexp_like(fruit, '(^a)|(^b)') / FRUIT ------ apple banana
If instead, we want to match the end of the string…
select fruit from fruits where regexp_like(fruit, '(ge$)|(on$)') / FRUIT ------ orange lemon
…and if you want to combine searching for patterns at the start, end or anywhere in a string, in this case searching for records that
- start with ‘o’
- or contain the string ‘ana’
- or end with the string ‘on’
select fruit from fruits where regexp_like(fruit, '(^o)|(ana)|(on$)') / FRUIT ------ banana orange lemon
Finally on this whistle-stop tour of REGEXP_LIKE, for a case insensitive search…
select fruit from fruits where regexp_like(fruit, '(^O)|(ANA)|(ON$)', 'i') / FRUIT ------ banana orange lemon
There’s quite a bit more to regular expressions in Oracle SQL.
For a start, here’s an example of using REGEXP_LIKE to validate a UK Post Code.
There’s also a comprehensive guide here on the PSOUG site.
Now I’ve gone through all that fruit I feel healthy enough for a quick jog… to the nearest pub.
I wonder if that piece of lime they put in top of a bottle of beer counts as one of my five a day ?