Compare fields Where One is Similar to Part of Another in Postgres sql

Question

I would like to write a Select query where I can see if one column is like part of another column in different table postgresql.

tblNames1:

ID	Description	target table
1	select test.fn_hierarchy_prod_group	test_1
2	select run_update_query	test_2
3	select func_datad_addr_1	test_3
4	select func_datad_addr	test_4

tblNames2:

ID	Description	target table
1082	fn_hierarchy_prod_group	dba.l
1091	func_datad_addr	dba.n
1099	fn_hierarchy_customer	dba.m
1100	run_update_query	dba.j

The query should return and update target tblnames1:

ID	Description	target table
1	select test.fn_hierarchy_prod_group	dba.l
2	select run_update_query	dba.j
3	select func_datad_addr_1	test_3
4	select func_datad_addr	dba.n

I am new to postgres and have search different ways to update it but I am not getting success.I have tried using query like

select *
from tblnames1 a 
left join tblnames2 b on 1=1 
where b.target_table like concat('%',a.target_table,'%')

But this query is wrong.

Answer 1

1. According to the schema you showed, you want to join on regex-matched Description columns, not target_tables.
2. You want the shorter Description with just the object identifier to be in the regular expression, and look for that in the longer Description with the SQL select statements in it.

Demo at db<>fiddle:

select a.id, a.description, b.target_table
from tblNames1 a 
left join tblNames2 b
  on a.description ilike concat('%',b.description,'%');

id	description	target_table
1	select test.fn_hierarchy_prod_group	dba.l
2	select run_update_query	dba.j
3	select func_datad_addr_1	dba.n
4	select func_datad_addr	dba.n

4. You probably want case-insensitive ilike, not like.
5. Depending on where those select statements in the other Description column come from, you might have a bad time matching them if they use varying case and combine that with varying double quoting (unquoted are folded to lowercase, quoted preserve original case) - in that case ilike will be wrong.
6. In PostgreSQL 14+, some view/routine/column dependencies can be found in information_schema views with _usage in their name if that's what you're after. Some more insight could be acquired in the system catalogs. The demo shows how plain SQL functions with unquoted bodies

   create function f() returns text BEGIN ATOMIC SELECT result FROM table1; END;
   create function f() returns text RETURNS (SELECT result FROM table1);
   

   get listed in routine_table_usage. Unfortunately, it doesn't track those with quoted bodies

   create function f() returns setof int AS $f$SELECT result FROM table1$f$;
   

   so you do need to run regular expressions against the routine definition in those cases.