Madhava Madhava -4 years ago 121
SQL Question

How to display string found in title except exception list in SQL

I have following scenario

"Original" word found in Title column except below exceptions

sample column(title) values like -


  • Original Motion Picture

  • Amazing Grace (Original Broadway Cast
    Recording)



exception list:

Original%Cast ,Original%Broadway, Original%Motion, Original%Score, Original%Sound, Original%Game ,Original%TV, Original%Television, Original%Off, Original%Series,


I have written sample query like this

select count(1)
from table_name
where product_id = :PRODUCT_ID
and instr(upper(title),'ORIGINAL') > 0
and a
(
title not like '%Original%Cast%'
--or title not like 'Original%Broadway'
--or title not like ....

);


Please suggest the query with regular expression as shown list
in('%Original%Cast%','Original%Broadway','....)
in clause

Answer Source

Here's one way:

WITH table_name AS (SELECT 'The Original Case' title FROM dual UNION ALL
                    SELECT 'The First Case' title FROM dual UNION ALL
                    SELECT 'The Super Case' title FROM dual UNION ALL
                    SELECT 'The Super Case - Original Cast Recording' title FROM dual UNION ALL
                    SELECT 'The Super Case - Original Broadway Show' title FROM dual UNION ALL
                    SELECT 'The Super Case - Original Motion Picture' title FROM dual UNION ALL
                    SELECT 'The Super Case - Original Score' title FROM dual UNION ALL
                    SELECT 'The Super Case - Original Soundtrack' title FROM dual UNION ALL
                    SELECT 'The Super Case - Original Video Game' title FROM dual UNION ALL
                    SELECT 'The Super Case - Original TV show' title FROM dual UNION ALL
                    SELECT 'The Super Case - Original television show' title FROM dual UNION ALL
                    SELECT 'The Super Case - Original off-site production' title FROM dual UNION ALL
                    SELECT 'The Super Case - Original theatre series' title FROM dual UNION ALL
                    SELECT 'The TV Original Case' title FROM dual)
SELECT *
FROM   table_name
WHERE  NOT regexp_like(title, 'original.*(cast|broadway|motion|score|sound|game|tv|television|off|series)', 'i')
AND    regexp_like(title, 'original', 'i');

TITLE
---------------------------------------------
The Original Case
The TV Original Case

The first regexp_like looks for a pattern of "original" followed by any character, then followed by either cast, broadway, ...., or series. The i in the modifier parameter means the search is case insensitive. Then we add the NOT in front of it to discount rows with that pattern.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download