Skip to main content
added 152 characters in body
Source Link
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

I would start with a helper function to generate your query terms from the text-string input. You could inlinedo something like this into,

  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, but it's ugly and silly and if you need it here then you probably need it elsewhere.

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 (VALUES ('foo hello-world helloXworld') ) AS query(q)
CROSS JOINLATERAL unnest(x) AS t2(word)
WHERE NOT word LIKE ANY(my_stupid_query(q'foo hello-world helloXworld'));

 word 
------
 bar
(1 row)

I would start with 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.

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()

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

 word 
------
 bar
(1 row)

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)
added 6 characters in body
Source Link
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

I would start with 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.

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()

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

 word 
------
 bar
(1 row)

I would start with a helper function to generate your query 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.

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()

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

 word 
------
 bar
(1 row)

I would start with 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.

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()

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

 word 
------
 bar
(1 row)
Source Link
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

I would start with a helper function to generate your query 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.

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()

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

 word 
------
 bar
(1 row)