How to replace value text in sql query during search?

Question

PostgreSQL

There are two tables.
First:
Name_short | Value
JSC "SZP" | 10
Second:
Name_full
Joint-Stock Company "Saransk Plant Pupkin"
I say right away - If id could be made a join on it, but it is not.
Search input: JSC "Saransk Pupkin Plant"
We will not find anything, not in the first, not in the second table.
Is it possible, for example, during the search to replace words with words from the list, for example:
Spisok=([JSC; joint-stock company], [JSC; Closed Joint-Stock Company], [LLC; Limited Liability Company]);
We are looking for:
JSC "Saransky Zavod Pupkina"
We do not find and change during the search for JSC with the full name of
Profit
The fact is that it is impossible to cut off the beginning and search purely by name without a legal form, since invalid names such as ZAO or OA will be issued due to the occurrence of AO in these words.
In sql, I am not familiar with lists, arrays and variables, and I can’t sort through the data from here. I know how to do the Replace function, but it turns out a construction like a request in a request, and even 'ao', as letters in a word can occur. Any ideas?

Answer 1

I looked at the task. It seems to me that this problem is solved by using a correspondence table of long and short legal forms. Here is the script:

--таблица с заводами
create table test_tbl 
(
Name_full text
);

insert into test_tbl 
select 'Акционерное общество "Саранский завод Пупкина"';



--таблица соответствий правовых форм
create table prav_forms 
(
short_form text,
full_form text
);

--заполняем таблицу
insert into prav_forms(short_form,full_form)
select 'ао','акционерное общество'
union
select 'зао','закрытое акционерное общество'
union 
select 'ооо','общество с ограниченной ответственностью';


--выходной запрос

with ish as(
--что ищем
select 'ао "Саранский завод Пупкина"'::text  as search_str
),
ish2 as (
--получаем форму и завод регулярками
select (regexp_matches(search_str, '(\w+)'))[1] as form,
(regexp_matches(search_str, '\"[\w\s]+\"'))[1] as form2
 from ish
)
,
ish3 as (
--находим запись по короткому наименованию 
select * from test_tbl where lower(Name_full)=(select lower(search_str) from ish limit 1)
)
--выборка
select * from ish3
union
--если по короткому не найдено, то получаем полную форму и ищем по ней
select * from test_tbl tt
where not exists(select * from ish3)
and lower(tt.Name_full) in (select 
lower(pv.full_form || ' ' || ish2.form2)
 from ish2 inner join prav_forms pv on 
lower(pv.short_form)=lower(ish2.form)
limit 1
);