regexoracleplsql

Filter Data by strings in Oracle


In a table tab1, there is data like below.

Col1   Col2  Message (Value can be concatenation of multi messages string after "Hello there" can vary)
-----------------------------------------------------------
I1     L1    Message1|Message2|Hello there D1,D2,D3
I2     L2    Message1|MessageN|Hello there D3,D4,D1
I3     L3    Hello there D3,D5
I4     L4    Message3|Message4
I5     L5    Hello there DN,DN+1

I need to select only records with 'Hello there' string and no other messages like Message 1,2 .. N etc., (only 3rd and 5th records should be selected)

Can anybody help with effective way of doing this? I am trying like below, but it is not giving the desired result.

select * from tab1 t1 where message like '%Hello there%' 
and not exists (select 1 from tab1 t2 where t1.col1=t2.col1 and t1.col2 =t2.col2 and ???)

Solution

  • Find the strings with only a single term (i.e. without the term delimiter) that start with your desired phrase:

    SELECT *
    FROM   tab1
    WHERE  message LIKE 'Hello there%'
    AND    message NOT LIKE '%|%'
    

    or, probably less efficiently:

    SELECT *
    FROM   tab1
    WHERE  REGEXP_LIKE(message, '^Hello there[^|]*$')
    

    Which, for the sample data:

    CREATE TABLE tab1 (Col1, Col2, Message) AS
    SELECT 'I1', 'L1', 'Message1|Message2|Hello there D1,D2,D3' FROM DUAL UNION ALL
    SELECT 'I2', 'L2', 'Message1|MessageN|Hello there D3,D4,D1' FROM DUAL UNION ALL
    SELECT 'I3', 'L3', 'Hello there D3,D5' FROM DUAL UNION ALL
    SELECT 'I4', 'L4', 'Message3|Message4' FROM DUAL UNION ALL
    SELECT 'I5', 'L5', 'Hello there DN,DN+1' FROM DUAL UNION ALL
    SELECT 'I6', 'L6', 'Hello there DN,DN+1|Message6' FROM DUAL;
    

    Both output:

    COL1 COL2 MESSAGE
    I3 L3 Hello there D3,D5
    I5 L5 Hello there DN,DN+1

    fiddle