Oracle Get Records where String not present as Sub string in other rows?

user_3825444 0
Viewed 155

Hi This is my problem:-

Value
-----
ABCDE
ABCD
ABC
ABF
ABFG

Result should be : ABCDE & ABFG

above are not substrings of any other string with in the same column without recurs

substring plsql oracle sql
2 Answers

You can do:

select *
from my_table t1
where not exists (
  select 1 from my_table t2
  where t1.value <> t2.value 
    and t2.value like '%' || t1.value || '%'
)
user_64361910

This can be achieved with a LEFT JOIN combined with a WHERE .., IS NULL.

Compared to the correlated query solution, the LEFT JOIN query has a shorter/cleaner syntax, and often performs better when dealing with large datasets.

SELECT t1.value
FROM 
    my_table t1
    LEFT JOIN my_table t2 
        ON t2.value <> t1.value 
        AND t2.value LIKE '%' || t1.value || '%'
WHERE t2.value IS NULL
user_106767160
Related