By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tab as
select 'AAA' col from dual union all
select 'AABA' col from dual union all
select 'COL1' col from dual union all
select 'COL21' col from dual union all
select 'AAAAAA' col from dual union all
select 'BBAA' col from dual union all
select 'BAAAA' col from dual union all
select 'AB' col from dual union all
select 'AC1' col from dual union all
select 'AD' col from dual union all
select 'ZC' col from dual union all
select 'Y' col from dual
12 rows affected
with potential_abbreviations(col, abbr, lev) as (
select col, col as abbr, 1 as lev
from tab
union all
select pa.col, substr(pa.abbr, 1, length(pa.abbr) - 1) as abbr, lev + 1
from potential_abbreviations pa
where length(abbr) > 1 and
not exists (select 1
from tab
where tab.col like substr(pa.abbr, 1, length(pa.abbr) - 1) || '%' and
tab.col <> pa.col
)
)
select pa.col, pa.abbr
from (select pa.*, row_number() over (partition by pa.col order by pa.lev desc) as seqnum
from potential_abbreviations pa
) pa
where seqnum = 1
| COL | ABBR |
|---|---|
| AAA | AAA |
| AAAAAA | AAAA |
| AABA | AAB |
| AB | AB |
| AC1 | AC |
| AD | AD |
| BAAAA | BA |
| BBAA | BB |
| COL1 | COL1 |
| COL21 | COL2 |
| Y | Y |
| ZC | Z |