16

REGEXP_LIKE – Happy thoughts whilst searching for multiple substrings in Oracle ...

 3 years ago
source link: https://mikesmithers.wordpress.com/2017/08/17/regexp_like-happy-thoughts-whilst-searching-for-multiple-substrings-in-oracle-sql/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

REGEXP_LIKE – Happy thoughts whilst searching for multiple substrings in Oracle SQL

Posted on August 17, 2017

International relations seem to be somewhat tense at the moment with various World Leaders being publicly grumpy with each other.
To keep my mind off damoclesian digits dangling dangerously over Big Red Shiny Buttons, I really just want to hear some nice news to help me think happy thoughts.

I’d like to read about Luton Town because they’re doing quite well, or the England Cricket team winning a Test Series. I might even treat myself to a random Cat Video…

In the olden days (or when I forgot the appropriate syntax), if I wanted a query to reflect these preferences I would have had to write something like :

with news as
(
select 'More BREXIT misery predicted' as headline from dual union all
select 'Luton start the season with an 8-2 win' from dual union all
select q'["We're going to build that wall", says Trump]' from dual union all
select 'Moeen stars as England defeat South Africa' from dual union all
select 'Putin to go on wall-climbing holiday in Mexico' from dual union all
select q'["We're knocking down that wall!" says Kim]' from dual union all
select 'Fluffy kitten on YouTube makes everyone go "Aaawwww"' from dual union all
select 'UK Weather forecast : More rain' from dual
)
select headline
from news
where
(
lower(headline) like '%luton%'
or lower(headline) like '%moeen%'
or lower(headline) like '%fluffy%'
)
/

Sure, it does the job, but there must be a better way…

with news as
(
select 'More BREXIT misery predicted' as headline from dual union all
select 'Luton start the season with an 8-2 win' from dual union all
select q'["We're going to build that wall", says Trump]' from dual union all
select 'Moeen stars as England defeat South Africa' from dual union all
select 'Putin to go on wall-climbing holiday in Mexico' from dual union all
select q'["We're knocking down that wall!" says Kim]' from dual union all
select 'Fluffy kitten on YouTube makes everyone go "Aaawwww"' from dual union all
select 'UK Weather forecast : More rain' from dual
)
select headline
from news
where regexp_like( headline, '(luton|moeen|fluffy)', 'i')
/

As usual with regular expressions, it’s probably worth reviewing each part of the expression in case your either new to them, or simply forgetful, like me…

regexp_like(headline, - The source of the string we want to search through
'(luton|moeen|fluffy)', a list of substrings to search for - we're looking for any of these
'i' - do a case-insensitive search

If you want to delve a bit deeper into the wonders of REGEXP_LIKE, have a look at the Oracle documentation here.

The result is just as good for my stress-levels….

HEADLINE                                           
----------------------------------------------------
Luton start the season with an 8-2 win             
Moeen stars as England defeat South Africa         
Fluffy kitten on YouTube makes everyone go "Aaawwww"

…but requires a lot less typing.

I’ve covered some of the functionality avaialble in Oracle via the REGEXP functions previously. However, Morgan’s Library is still my favourite Oracle Regular Expression De-mistifyer.
In the meantime, I’m off to my happy place 🙂

This entry was posted in Oracle, SQL and tagged case insensitive search for multiple substrings, regexp_like by mikesmithers. Bookmark the permalink.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK