---

with src as (
    select naimen,
           trim(
             regexp_replace(
               -- сначала отрезаем номера счетов, потом хвост реквизитов
               regexp_replace(trim(naimen), '[[:space:]]*[0-9]{9,}.*$', ''),
               '[[: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(clean_name) as r,  -- для LLC, LTD и т.п.
           -- верхний регистр, один пробел, латиница и 0 -> кириллица
           translate(
               regexp_replace(upper(clean_name), '[[:space:]]+', ' '),
               'ABCEHKMOPTXY0',
               'АВСЕНКМОРТХУО'
           ) as n
    from src
),
flag as (
    select naimen,
           clean_name,
           -- 1 = название обрезано полем (длина 50) и хвост не вырезался
           case when length(naimen) >= 50
                 and clean_name = trim(naimen) then 1 else 0 end as is_cut,
           regexp_replace(n, '[^А-ЯЁA-Z0-9]', '') as k,
           case when length(clean_name) >= 5
                 -- ИП не считаем
                 and not regexp_like(n,
                       '^[^А-ЯЁA-Z0-9]*(ИП|ЧП|ИНДИВИДУАЛЬНЫЙ ПРЕДПРИНИМАТЕЛЬ)([^А-ЯЁA-Z0-9]|$)')
                 and (
                      -- 1) однозначные формы: в любом месте строки
                      regexp_like(n,
                        '(^|[^А-ЯЁA-Z0-9])(ООО|ОАО|ЗАО|ПАО|НАО|ТОО|ФГУП|МУП|ГУП|ГБУ|ГКУ|ГАУ|АНО|НКО|СПК|КФХ|РПО|СНТ|ТСЖ|ЖСК)([^А-ЯЁA-Z0-9]|$)')
                      -- 2) короткие неоднозначные: только в начале или в конце
                   or regexp_like(n,
                        '^[^А-ЯЁA-Z0-9]*(АО|ПК|КБ|АКБ)([^А-ЯЁA-Z0-9]|$)')
                   or regexp_like(n,
                        '(^|[^А-ЯЁA-Z0-9])(АО|ПК|КБ|АКБ)[^А-ЯЁA-Z0-9]*$')
                      -- 3) полные названия (в т.ч. обрезанные)
                   or regexp_like(n,
                        'ОБЩЕСТВО С ОГРАНИЧЕННОЙ|АКЦИОНЕРНОЕ ОБЩЕСТВО|УНИТАРНОЕ ПРЕДПРИЯТИЕ')
                      -- 4) иностранные формы
                   or regexp_like(r,
                        '(^|[^A-ZА-ЯЁ0-9])(LLC|LTD|JSC|PJSC|INC|GMBH)([^A-ZА-ЯЁ0-9]|$)')
                      -- 5) название в кавычках (кроме физлиц с инициалами)
                   or (regexp_like(clean_name,
                         '["«»„“”`<>''].{2,}["«»„“”`<>'']')
                       and not regexp_like(clean_name,
                         '^[А-ЯЁ][А-ЯЁа-яё-]+[[:space:]]+[А-ЯЁ]\.', 'c'))
                 )
                then 1 else 0 end as is_org
    from norm
),
cand as (
    select /*+ materialize */ clean_name, is_cut, k
    from flag
    where is_org = 1
)
select min(c.clean_name) as "Наименование"
from cand c
where c.is_cut = 0
   or not exists (
        select 1
        from cand x
        where x.k like c.k || '_%'   -- есть более длинная запись с тем же началом
      )
group by c.k
order by 1;



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






create table spr_gorod as
with src as (
    select -- латинские двойники -> кириллица, чтобы поймать "Г.МOСКВА" со смешанной раскладкой
           translate(upper(trim(naimen)), 'ABCEHKMOPTXY', 'АВСЕНКМОРТХУ') as u,
           length(naimen) as len
    from sh.uch_vse
    where naimen is not null
),
ext as (
    select u, len,
           coalesce(
               -- двусловные города, где первое слово из списка (список можно дополнять)
               regexp_substr(u,
                 '(^|[^А-ЯЁ])(Г|ГОР|ГОРОД|ГОРОДА)[. ]+((НИЖНИЙ|НОВЫЙ|НОВАЯ|СТАРЫЙ|СТАРАЯ|ВЕЛИКИЙ|ВЕЛИКИЕ|НАБЕРЕЖНЫЕ|УСТЬ|ЙОШКАР)[ -][А-ЯЁ]{2,})',
                 1, 1, 'c', 3),
               -- обычные: одно слово или через дефис (САНКТ-ПЕТЕРБУРГ, РОСТОВ-НА-ДОНУ)
               regexp_substr(u,
                 '(^|[^А-ЯЁ])(Г|ГОР|ГОРОД|ГОРОДА)[. ]+([А-ЯЁ]{2,}(-[А-ЯЁ]{1,}){0,3})',
                 1, 1, 'c', 3)
           ) as raw
    from src
),
clean as (
    select regexp_replace(replace(raw, 'Ё', 'Е'), '[[:space:]]+', ' ') as city
    from ext
    where raw is not null
      -- отбрасываем город, если строка обрезана полем на самом городе (Г.МОСК)
      and not (len >= 50
               and regexp_like(u, '(^|[^А-ЯЁ])(Г|ГОР|ГОРОД|ГОРОДА)[. ]+[А-ЯЁ -]*$'))
),
uniq as (
    select city
    from clean
    where length(city) >= 3
    group by city
    -- having count(*) >= 2   -- раскомментируйте, чтобы убрать редкий мусор
)
select row_number() over (order by city) as id,
       city as name
from uniq;

-- проверка с частотами (редкие значения обычно мусор или опечатки):
-- select city, count(*) cnt from ( ...тот же clean... ) group by city order by cnt desc;