PostgreSQL Reverse LIKE

Question

I need to test if any part of a column value is in a given string, instead of whether the string is part of a column value. For instance:

This way, I can find if any of the rows in my table contains the string 'bricks' in column:

SELECT column FROM table
WHERE column ILIKE '%bricks%';

But what I'm looking for, is to find out if any part of the sentence "The ships hung in the sky in much the same way that bricks don’t" is in any of the rows. Something like:

SELECT column FROM table
WHERE 'The ships hung in the sky in much the same way that bricks don’t' ILIKE '%' || column || '%';

So the row from the first example, where the column contains 'bricks', will show up as result.

I've looked through some suggestions here and some other forums but none of them worked.

@Lamak I think they want to split out the sentence into separate words and check to see if any of those words, in any order, is in the table column. I would think we would need to split the string into rows and then simply check if each exists — xQbert
– xQbert, Commented Aug 17, 2017 at 14:44
@douglas_forsell I'd use stackoverflow.com/questions/29419993/… to split the string to rows to start and then go from there. via join etc. — xQbert
– xQbert, Commented Aug 17, 2017 at 14:50
Do you really want to match 'bricks', not the word stem 'brick'? And do you really want to consider noise words like 'the' or 'in'? And what's contained in column exactly? As always, start by providing your Postgres version and exact table definition. — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 17, 2017 at 16:07

Erwin Brandstetter · Accepted Answer · 2023-08-01 21:55:12Z

Your simple case can be solved with a simple query using the ANY construct and ~*:

SELECT *
FROM   tbl
WHERE  col ~* ANY (string_to_array('The ships hung in the sky ... bricks don’t', ' '));

~* is the case insensitive regular expression match operator. I use that instead of ILIKE so we can use original words in your string without the need to pad % for ILIKE. The result is the same - except for words containing special characters: %_\ for ILIKE and !$()*+.:<=>?[\]^{|}- for regular expression patterns. You may need to escape special characters either way to avoid surprises. Here is a function for regular expressions:

Escape function for regular expression or LIKE patterns

But I have nagging doubts that will be all you need. See my comment. I suspect you need Full Text Search with a matching dictionary for your natural language to provide useful word stemming ...

krokodilko · Accepted Answer · 2017-08-17 15:02:39Z

This query:

SELECT 
regexp_split_to_table(
  'The ships hung in the sky in much the same way that bricks don’t', 
  '\s' );

gives a following result:

| regexp_split_to_table |
|-----------------------|
|                   The |
|                 ships |
|                  hung |
|                    in |
|                   the |
|                   sky |
|                    in |
|                  much |
|                   the |
|                  same |
|                   way |
|                  that |
|                bricks |
|                 don’t |

Now just do a semijoin against a result of this query to get desired results

SELECT * FROM table t
WHERE EXISTS (
   SELECT * FROM (
      SELECT 
    regexp_split_to_table(
      'The ships hung in the sky in much the same way that bricks don’t', 
      '\s' ) x
   ) x
   WHERE t.column LIKE '%'|| x.x || '%'
)

Collectives™ on Stack Overflow

PostgreSQL Reverse LIKE

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related