Postgresql – Querying an array of strings with a string that represents search terms

arraypostgresqlstring-searchingstring-splitting

I have an array of values like:

array ('hello world', 'foo', 'bar') -- terms here have only letters and one space at most

and some text like: 'foo hello-world hello1world'

and I would like to get back from the original array the values foo and hello world filtering out bar.

Is there any way to get that with Postgres?

Best Answer

I would do something like this,

  1. create a helper function to generate your query terms from the text-string input. You could inline this into the function, but it's ugly and silly and if you need it here then you probably need it elsewhere.
  2. Write a query with NOT word LIKE ANY()

Here is a demo of the helper function,

CREATE OR REPLACE FUNCTION my_stupid_query(q text)
RETURNS text[] AS $$

  SELECT ARRAY(
    SELECT regexp_replace(t, '[^[:alpha:]]', '_', 'g')
    FROM unnest(string_to_array(q, ' ')) AS q(t)
  );

$$ LANGUAGE sql
IMMUTABLE;

Then you can use NOT word LIKE ANY(). This shows what it would look like,

SELECT word
FROM (VALUES (ARRAY['hello world', 'foo', 'bar'])) AS t1(x)
CROSS JOIN LATERAL unnest(x) AS t2(word)
WHERE NOT word LIKE ANY(my_stupid_query('foo hello-world helloXworld'));

 word 
------
 bar
(1 row)
Related Question