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 ???)
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 |