How to check if a substring is in any array value in Postgres?

Question

So I have the following use case:

I have three values street_a, number_a and address_list of type varchar like this:

address_a = 'Main Street'
number_a = '4'
address_list = 'Lower Street 6;Main Street 3,4,5'

Now I would like to return a TRUE when I check the concatenated strings of street_a and number_a (Main Street 4) as it is part of address_list. I hoped that the following would work:

SELECT 
    lower(REPLACE(street_a, ' ', '')) || '%,' || number_a || ',%' 
    LIKE ANY 
    (string_to_array(lower(REPLACE(address_list, ' ','')), ';'))

However, the % signs in a LIKE statement are only applied when on the right side of the check. When on the left side they are just concatenated to the strings...

EDIT

And because of other cases like

address_list = 'Main Street 1,2;Lower Street 4'

I cannot do

SELECT 
    lower(REPLACE(address_list, ' ',''))
    LIKE
    lower(REPLACE(street_a, ' ', '')) || '%' || number_a || '%'  

Is there a way to return TRUE for my case here?

Answer 1

I'd say that this is not the best way to store addresses, or do lookups. There's a ton of complexities in addresses, for which this approach is not well suited. I'd advise against going this route at all. However, if you're forced to do so, then I think using a regular expression may be a better approach than a LIKE expression.

Note: I'm not addressing the possibility that the street or number could possibly contain characters that should be escaped, or many other scenarios, but this code "could" work under certain circumstances.

CREATE OR REPLACE FUNCTION address_match(street_a text, number_a text, address_list text) RETURNS BOOLEAN LANGUAGE plpgsql AS     
$$ 
declare
  pattern text := replace(street_a, ' ', '') || '(([0-9]+,)*)' || number_a || '(,.*)?$'; 
  result bool;
begin
  with a as (SELECT unnest(string_to_array(replace(address_list,  ' ', ''), ';')) as address) 
  select exists(select a.address from a where a.address ~* pattern) into result;
  return result;
end;
$$;

Some quick test scenarios ...

postgres=# select address_match('Main Street', '3', 'Lower Street 6;Main Street 3,4,5');
 address_match 
---------------
 t
(1 row)

postgres=# select address_match('Main Street', '4', 'Lower Street 6;Main Street 3,4,5');
 address_match 
---------------
 t
(1 row)

postgres=# select address_match('Main Street', '5', 'Lower Street 6;Main Street 3,4,5');
 address_match 
---------------
 t
(1 row)

postgres=# select address_match('maiN StreeT', '3', 'Lower Street 6;Main Street 3,4,5');
 address_match 
---------------
 t
(1 row)

postgres=# select address_match('Main Street', '33', 'Lower Street 6;Main Street 3,4,5');
 address_match 
---------------
 f
(1 row)

postgres=# select address_match('Main Street', '45', 'Lower Street 6;Main Street 3,4,5');
 address_match 
---------------
 f
(1 row)

postgres=# select address_match('maiN StreeT', '1', 'Lower Street 6;Main Street 3,4,5');
 address_match 
---------------
 f
(1 row)

postgres=# select address_match('Lower    StreeT', '6', 'Lower Street 6;Main Street 3,4,5');
 address_match 
---------------
 t
(1 row)

postgres=# select address_match('Lower    StreeT', '3', 'Lower Street 6;Main Street 3,4,5');
 address_match 
---------------
 f
(1 row)