https://pastein.ru/t/8xN
Загрузка данных
-- ============================================================
-- FIO, которые есть в СТАРОМ запросе (2086), но пропали в НОВОМ (1283)
-- ============================================================
SELECT FIO
FROM (
-- ==================== СТАРЫЙ (ваш исходный) ====================
SELECT
COUNT(*) OVER () AS всего_строк,
FIO
FROM (
WITH t AS (
SELECT
REGEXP_SUBSTR(
REGEXP_REPLACE(
NAIMEN,
'^((КПП[[:space:]]+[0-9]+|КПП|ПРЕДПРИНИМАТЕЛЬ[А-Яа-яЁё]*|ПЛЮЙЛ|ИП|ЧП|ФЛ|[0-9]+)[[:space:]]+)+',
'', 1, 0, 'i'
),
'^[А-ЯЁ][А-Яа-яЁё\-]+'
|| '('
|| '[[:space:]]+[А-ЯЁ]\.[[:space:]]*[А-ЯЁ]\.'
|| '|[[:space:]]+[А-ЯЁ][А-Яа-яЁё\-]+'
|| '([[:space:]]+[А-ЯЁ][А-Яа-яЁё\-]+)?'
|| '([[:space:]]+(ОГЛЫ|ОГЛУ|КЫЗЫ|УГЛЫ|УУЛУ|ГИРЕЙ|ХАН|БЕК|БАЙ))?'
|| ')'
) AS FIO
FROM SH.UCH_VSE
WHERE
NAIMEN IS NOT NULL
AND NOT REGEXP_LIKE(NAIMEN,
'(ООО|ОАО|ПАО|ЗАО|АО|ОДО|ОСБ|АКБ|КБ|КПП|ГУП|МУП|ФГУП|ФУ|КОРПОРАЦИЯ|СОФТ|ОБЪЕДИНЕНИЕ|ИНТЕРНЭШНЛ|МЭП|ХОЗ-ВО|РУФПС|НЕВЫЯСНЕ[А-Яа-яЁё]*|ПАРТНЕР[А-Яа-яЁё]*|ПАЛАТА|ОАНО|ОБЪЕДИ[А-Яа-яЁё]*|УЗЕЛ)',
'i')
AND NOT REGEXP_LIKE(NAIMEN,
'(ФОНД|БАНК|КОМПАНИЯ|ПРЕДПРИЯТИЕ|ОБЩЕСТВО|СОЮЗ|ВКЛАДЫ|ГРАЖДАН|КОРПОРАЦИ[А-Яа-яЁё]*|ИНСТИТУТ|ПОДРАЗДЕЛЕНИЕ|СЕТИ|ОТД|МЕДИА|КЛУБ|РАЙПО|УСИБ|ОБЩЕСТВЕННОЕ|ОБЩ-ВО|ОБЩЕСТ[А-Яа-яЁё]*|ЕДИНЕНИЕ|РОССИЙСК[А-Яа-яЁё]*)',
'i')
AND NOT REGEXP_LIKE(NAIMEN,
'(АДМИНИСТРАЦ[А-Яа-яЁё]*|УПРАВЛЕНИ[А-Яа-яЁё]*|ДЕПАРТАМЕНТ[А-Яа-яЁё]*|КОМИТЕТ[А-Яа-яЁё]*|МИНИСТЕРСТВ[А-Яа-яЁё]*|ОТДЕЛЕНИЕ|ПОЛИТИЧ[А-Яа-яЁё]*|ПОСОЛЬСТВО|ПРЕДСТАВИТ[А-Яа-яЁё]*|ПОДВОРЬЕ|КОНСУЛЬТАТЦИЯ)',
'i')
AND NOT REGEXP_LIKE(NAIMEN,
'(ФЕДЕРАЛЬН[А-Яа-яЁё]*|ГОСУДАРСТВЕНН[А-Яа-яЁё]*|МЕЖДУНАРОДН[А-Яа-яЁё]*|ОРГАНИЗАЦИ[А-Яа-яЁё]*|ФИЛИАЛ|ЗАВОД|ПРОИЗВОДСТВЕНН[А-Яа-яЁё]*|ОБЛПО|РАЙОН[А-Яа-яЁё]*|ГРУППА|СЧЕТ|РЕДАКЦИЯ|НПФ|ГП)',
'i')
AND NOT REGEXP_LIKE(NAIMEN,
'(ГОСТИНИЦА|ПТИЦЕФАБРИК[А-Яа-яЁё]*|ФАБРИК[А-Яа-яЁё]*|ЗАКРЫТ[А-Яа-яЁё]*|ОТКРЫТ[А-Яа-яЁё]*|АКЦИОНЕРН[А-Яа-яЁё]*|МОСКОВСК[А-Яа-яЁё]*|СНГ|ПАРТИЯ|БЮРО|ЕДИНИЦА|Ф-Л|ТУРИЗМ|УМП|ДОМ|ТОВАРИЩЕСТВО|ФИН)',
'i')
AND NOT REGEXP_LIKE(NAIMEN,
'(ЖСК|ТСЖ|ВДПО|ГМБХ|МИН?ФИН|ИНДИВИД[А-Яа-яЁё]*|ЧАСТН[А-Яа-яЁё]*|ЧП|ИП|РОС-Я|АРТЕЛЬ|МАШИНОСТРО[А-Яа-яЁё]*|ГОРОД[А-Яа-яЁё]*|СЕВЕР[А-Яа-яЁё]*|СИСТЕМС|СОДЕКСО|З-Д|Ф-Т|ЛИМИТЕД|ЯЖНО-[А-Яа-яЁё]*|ГОС)',
'i')
AND NOT REGEXP_LIKE(NAIMEN,
'(ФИРМ[А-Яа-яЁё]*|ЦЕНТР[А-Яа-яЁё]*|ПРЕДПРИНИМАТЕЛЬ|ОТКРЫТОЕ|ЗАКРЫТОЕ|АКЦИОНЕРНОЕ|ГЛАВН[А-Яа-яЁё]*|КОМБИНАТ|КООПЕРАТИВ|ДИСТАНЦИЯ|ЖИЛЬЕ|ОТДЕЛЕНИЕ|ЛИЦ?|ХОЗЯЙСТВО|УПРАВ[А-Яа-яЁё]*|УЧАСТОК|ШАХТА)',
'i')
)
SELECT DISTINCT FIO
FROM t
WHERE
REGEXP_LIKE(FIO, '^[А-ЯЁ]', 'c')
AND REGEXP_LIKE(FIO, '[[:space:]]', 'c')
AND LENGTH(FIO) BETWEEN 6 AND 40
AND NOT REGEXP_LIKE(REGEXP_SUBSTR(FIO, '^[^[:space:]]+'), '^[А-ЯЁ]{2,3}$', 'c')
AND REGEXP_LIKE(
UPPER(REGEXP_SUBSTR(FIO, '^[^[:space:]]+')),
'(ОВ|ЕВ|ЁВ|ИН|ЫН|ИЙ|ЫЙ|ОЙ|СКИЙ|ЦКИЙ|СКАЯ|ЦКАЯ|'
|| 'ЕНКО|УК|ЮК|ЧУК|ЩУК|АК|ЯК|ИК|ИЧ|АЙ|ЯЙ|'
|| 'ШВИЛИ|ДЗЕ|ИДЗЕ|ЯН|ЯНЦ|ОГЛЫ|ОГЛУ|КЫЗЫ|УГЛЫ|УУЛУ|'
|| 'ГИРЕЙ|ХАН|БЕК|БАЙ|ЕЦ|ЕНОК|КО|ОВА|ЕВА|ИНА|ЫНА|ЁВА|'
|| 'О|А|Е|И|У|Ю|Я)$',
'i'
)
ORDER BY FIO
)
MINUS
-- ==================== НОВЫЙ (мой последний) ====================
SELECT
COUNT(*) OVER () AS всего_строк,
FIO
FROM (
WITH t AS (
SELECT
REGEXP_SUBSTR(
REGEXP_REPLACE(
REGEXP_REPLACE(
NAIMEN,
'^((КПП[[:space:]]+[0-9]+|КПП|ПРЕДПРИНИМАТЕЛЬ[А-Яа-яЁё]*|ИП|ЧП|ФЛ|[0-9]+)[[:space:]]+)+',
'', 1, 0, 'i'
),
'^((НОТАРИУС|ДИРЕКЦИЯ|УПРАВЛЕНИЕ|ФИЛИАЛ|ОТДЕЛЕНИЕ|АДМИНИСТРАЦИЯ|ПРЕДСТАВИТЕЛЬСТВО)[[:space:]]+)+',
'', 1, 0, 'i'
),
'(^|[[:space:]])[А-ЯЁ][А-Яа-яЁё\-]+'
|| '('
|| '[[:space:]]+[А-ЯЁ]\.[[:space:]]*[А-ЯЁ]\.'
|| '|[[:space:]]+[А-ЯЁ][А-Яа-яЁё\-]+'
|| '([[:space:]]+[А-ЯЁ][А-Яа-яЁё\-]+)?'
|| '([[:space:]]+(ОГЛЫ|ОГЛУ|КЫЗЫ|УГЛЫ|УУЛУ|ГИРЕЙ|ХАН|БЕК|БАЙ))?'
|| ')'
) AS FIO
FROM SH.UCH_VSE
WHERE NAIMEN IS NOT NULL
AND NOT REGEXP_LIKE(NAIMEN, '(ООО|ОАО|ПАО|ЗАО|АО|ОДО|ОСБ|АКБ|КБ|КПП|ГУП|МУП|ФГУП|ФУ|КПП|КБ|АО)', 'i')
AND NOT REGEXP_LIKE(NAIMEN, '(КОРПОРАЦИЯ|СОФТ|ОБЪЕДИНЕНИЕ|ИНТЕРНЭШНЛ|МЭП|ХОЗ-ВО|РУФПС)', 'i')
AND NOT REGEXP_LIKE(NAIMEN, '(ФОНД|БАНК|КОМПАНИЯ|ПРЕДПРИЯТИЕ|ОБЩЕСТВО|СОЮЗ|ВКЛАДЫ|ГРАЖДАН|КОРПОРАЦИ[А-Яа-яЁё]*)', 'i')
AND NOT REGEXP_LIKE(NAIMEN, '(ИНСТИТУТ|ПОДРАЗДЕЛЕНИЕ|СЕТИ|ОТД|МЕДИА|КЛУБ|РАЙПО|УСИБ|ОБЩЕСТВЕННОЕ|ОБЩ-ВО|ОБЩЕСТ[А-Яа-яЁё]*|ЕДИНЕНИЕ|РОССИЙСК[А-Яа-яЁё]*)', 'i')
AND NOT REGEXP_LIKE(NAIMEN, '(АДМИНИСТРАЦ[А-Яа-яЁё]*|УПРАВЛЕНИ[А-Яа-яЁё]*|ДЕПАРТАМЕНТ[А-Яа-яЁё]*|КОМИТЕТ[А-Яа-яЁё]*|МИНИСТЕРСТВ[А-Яа-яЁё]*|ОТДЕЛЕНИЕ|ПОЛИТИЧ[А-Яа-яЁё]*|ПОСОЛЬСТВО|ПРЕДСТАВИТ[А-Яа-яЁё]*|ПОДВОРЬЕ|КОНСУЛЬТАТЦИЯ)', 'i')
AND NOT REGEXP_LIKE(NAIMEN, '(ФЕДЕРАЛЬН[А-Яа-яЁё]*|ГОСУДАРСТВЕНН[А-Яа-яЁё]*|МЕЖДУНАРОДН[А-Яа-яЁё]*|ОРГАНИЗАЦИ[А-Яа-яЁё]*|ФИЛИАЛ|ЗАВОД|ПРОИЗВОДСТВЕНН[А-Яа-яЁё]*|ОБЛПО|РАЙОН[А-Яа-яЁё]*|ГРУППА|СЧЕТ|РЕДАКЦИЯ|НПФ|ГП)', 'i')
AND NOT REGEXP_LIKE(NAIMEN, '(ГОСТИНИЦА|ПТИЦЕФАБРИК[А-Яа-яЁё]*|ФАБРИК[А-Яа-яЁё]*|ЗАКРЫТ[А-Яа-яЁё]*|ОТКРЫТ[А-Яа-яЁё]*|АКЦИОНЕРН[А-Яа-яЁё]*|МОСКОВСК[А-Яа-яЁё]*|СНГ|ПАРТИЯ|БЮРО|ЕДИНИЦА|Ф-Л|ТУРИЗМ|УМП|ДОМ|ТОВАРИЩЕСТВО|ФИН)', 'i')
AND NOT REGEXP_LIKE(NAIMEN, '(ЖСК|ТСЖ|ВДПО|ГМБХ|МИН?ФИН|ИНДИВИД[А-Яа-яЁё]*|ЧАСТН[А-Яа-яЁё]*|ЧП|ИП|РОС-Я|АРТЕЛЬ|МАШИНОСТРО[А-Яа-яЁё]*|ГОРОД[А-Яа-яЁё]*|СЕВЕР[А-Яа-яЁё]*|СИСТЕМС|СОДЕКСО|З-Д|Ф-Т|ЛИМИТЕД|ГОС)', 'i')
AND NOT REGEXP_LIKE(NAIMEN, '(ФИРМ[А-Яа-яЁё]*|ЦЕНТР[А-Яа-яЁё]*|ПРЕДПРИНИМАТЕЛЬ|ОТКРЫТОЕ|ЗАКРЫТОЕ|АКЦИОНЕРНОЕ|ГЛАВН[А-Яа-яЁё]*|КОМБИНАТ|КООПЕРАТИВ|ДИСТАНЦИЯ|ЖИЛЬЕ|ХОЗЯЙСТВО|УПРАВ[А-Яа-яЁё]*|УЧАСТОК|ШАХТА)', 'i')
AND NOT REGEXP_LIKE(NAIMEN, '(НЕВЫЯСНЕ[А-Яа-яЁё]*|ПАРТНЕР[А-Яа-яЁё]*|ПАЛАТА|ОАНО|ОБЪЕДИ[А-Яа-яЁё]*|УЗЕЛ)', 'i')
)
SELECT DISTINCT FIO
FROM t
WHERE FIO IS NOT NULL
AND REGEXP_LIKE(FIO, '^[А-ЯЁ]', 'c')
AND REGEXP_LIKE(FIO, '[[:space:]]', 'c')
AND LENGTH(FIO) BETWEEN 6 AND 40
AND NOT REGEXP_LIKE(REGEXP_SUBSTR(FIO, '^[^[:space:]]+'), '^[А-ЯЁ]{2,3}$', 'c')
AND REGEXP_LIKE(
UPPER(REGEXP_SUBSTR(FIO, '^[^[:space:]]+')),
'(ОВ|ЕВ|ЁВ|ИН|ЫН|ИЙ|ЫЙ|ОЙ|СКИЙ|ЦКИЙ|СКАЯ|ЦКАЯ|'
|| 'ЕНКО|УК|ЮК|ЧУК|ЩУК|АК|ЯК|ИК|ИЧ|АЙ|ЯЙ|'
|| 'ШВИЛИ|ДЗЕ|ИДЗЕ|ЯН|ЯНЦ|ОГЛЫ|ОГЛУ|КЫЗЫ|УГЛЫ|УУЛУ|'
|| 'ГИРЕЙ|ХАН|БЕК|БАЙ|ЕЦ|ЕНОК|КО|ОВА|ЕВА|ИНА|ЫНА|ЁВА|'
|| 'О|А|Е|И|У|Ю|Я)$',
'i'
)
ORDER BY FIO
)
)
ORDER BY FIO;