CODEWITHSUNDEEP
sqloracle-database
Jaxox
Jaxox

Reputation: 968

SQL - Select the longest substrings

I have the data like that.

AB
ABC
ABCD
ABCDE
EF
EFG    
IJ
IJK
IJKL

and I just want to get ABCDE,EFG,IJKL. how can i do that oracle sql?

the size of the char are min 2 but doesn't have a fixed length, can be from 2 to 100.

Upvotes: 1

Views: 233

Answers (2)

shawnt00
shawnt00

Reputation: 17915

Select all rows where the string is not a substring of any other row. It's not clear if this is what you want though.

select t.str
from table t
where not exists (
    select 1
    from table t2
    where instr(t1.str, t2.str) > 0
);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269993

In the event that you mean "longest string for each sequence of strings", the answer is a little different -- you are not guaranteed that all have a length of 4. Instead, you want to find the strings where adding a letter isn't another string.

select t.str
from table t
where not exists (select 1
                  from table t2
                  where substr(t2.str, 1, length(t.str)) = t.str and
                        length(t2.str) = length(t.str) + 1
                 );

Do note that performance of this query will not be great if you have even a moderate number of rows.

Upvotes: 3

Related Questions