---

with src as (
    select naimen,
           regexp_replace(
               trim(naimen),
               '[[:space:]]+(' ||
                 '[РP]/[СC]' ||
                 '|([[:alpha:]]+[[:space:]]+)?ОСБ' ||
                 '|В[[:space:]]+(ОПЕРУ|ПУШКИНСК)' ||
                 '|ТРАНЗИТНЫЙ|ДОП\.|НЕВЫЯСНЕНН' ||
                 '|Ф/АЛ|ФИЛИАЛ|КАШИРСКИЙ' ||
                 '|Г\.[[:space:]]*[[:alpha:]]' ||
               ').*$',
               '', 1, 1, 'i'
           ) as clean_name
    from sh.uch_vse
    where naimen is not null
),
norm as (
    select naimen,
           clean_name,
           upper(trim(clean_name)) as r,
           translate(
               regexp_replace(upper(trim(clean_name)), '[[:space:]]+', ' '),
               'ABCEHKMOPTXY0',
               'АВСЕНКМОРТХУО'
           ) as n
    from src
),
flag as (
    select naimen,
           case when (
                    regexp_like(n,
                      '^(ООО|ОАО|ЗАО|ПАО|АО|НАО|ТОО|НКО|ФГУП|МУП|ГУП|ГБУ|ГКУ|ГАУ|АНО|ПК|СПК|КФХ|РПО|СНТ|ТСЖ|ЖСК|АКБ|КБ)([[:space:]"«„''‘’“”`<]|$)')
                 or regexp_like(n,
                      '^(ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ|(ПУБЛИЧНОЕ |ЗАКРЫТОЕ |ОТКРЫТОЕ )?АКЦИОНЕРНОЕ ОБЩЕСТВО)([[:space:]"«„''‘’“”`<]|$)')
                 or regexp_like(r,
                      '^(LLC|LTD|JSC|PJSC|INC|GMBH)([[:space:]"«„''‘’“”`<]|$)')
                  )
                and not regexp_like(clean_name, '^[А-ЯЁ][А-ЯЁа-яё-]+[[:space:]]+[А-ЯЁ]\.', 'c')
                and not regexp_like(clean_name, '[0-9]{9,}')
                and not regexp_like(n, '(^|[[:space:]"«])(БАНК|АКБ|КБ)([[:space:]"»]|$)')
                and length(naimen) between 5 and 300
                then 1 else 0 end as is_org
    from norm
)
select distinct naimen as "Наименование"
from flag
where is_org = 0
order by 1;