Standard SQL ? – Oracle REGEXP_LIKE

存储架构 2016-04-28

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

REGEXP_LIKE

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 ?

The Anti-Kyte

责编内容by:The Anti-Kyte (源链)。感谢您的支持!

您可能感兴趣的

MaxCompute Studio使用心得系列3——可视化分析作业运行... 上一篇写分享了通过MaxCompute Studio 编写sql脚本时“编译”功能带来的便捷,这次分享脚本编译好提交运行后,自助查看作业的执行情况。 我们...
出现 DB2 SQL Error: SQLCODE = -798, SQLSTATE = 428C9... 1 错误描述 在通过「DbVisualizer」执行 SQL 语句插入数据的时候,报出如下错误: 2 错误原因 通过观察上述标记出来的...
SQL Divide By Zero Error in SELECT statement I'm brand new to SQL and could use a hand. I'm getting a divide by zero error wi...
Understanding data sharing in SQL Server Docker co... In theprevious articles, we discussed a lot about SQL Server Docker containe...
An SQL query to display a list of sequences that h... I need to retrieve a list of sequences that contains a minimum of N occurrenc...