REGEXP_LIKE – Happy thoughts whilst searching for multiple substrings in Oracle ...
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
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK