Using Postgres SIMILAR TO with Spring Data to match substring in column

Question

I have a record in a column with the value "very nice BMW 4 wheel drive i5".

How can I make a query that will find this if I provide a substring of this value? Let's say I want to find it by providing this "BMW i5"?

If I use the LIKE method then it expects the words in order.

@Query("SELECT p FROM Product p WHERE p.name LIKE CONCAT('%',:name,'%')")

I'm using spring boot with Spring DATA. The database is postgres.

If I search the string on Postgres with this query I can find it, so the question how to do it with Spring DATA:

select name from product WHERE name similar TO '%((BMW) | (i5))%'

Erwin Brandstetter · Answer 1 · 2023-08-01T21:57:13.350

Using Postgres SIMILAR TO ...

Don't use SIMILAR TO. It's utterly pointless. See:

Finding partial matches anywhere in the string is faster with the regular expression match operator ~ (or possibly the case insensitive variant ~*). To match two strings in arbitrary order, use two expressions to keep it simple. Like:

SELECT * FROM Product p WHERE p.name ~ 'BMW' AND p.name ~ 'i5';

But I suspect you want 'i5' after 'BMW' (with some or no noise in between):

SELECT * FROM Product p WHERE p.name ~ 'BMW.*i5';

Or:

SELECT * FROM Product p WHERE p.name LIKE '%BMW%i5%';

Either can be supported with a trigram index like:

CREATE INDEX ON Product USING gin (name gin_trgm_ops);

Requires the additional module pg_trgm first. See:

PostgreSQL LIKE query performance variations

Or work with text search. Then you can pass one string with multiple words (lexemes). Your query can look like:

... WHERE to_tsvecor(p.name) @@ plainto_tsquery('simple', 'BMW i5');

Or use phrase search to find 'BMW' followed by 'i5':

... WHERE to_tsvecor(p.name) @@ phraseto_tsquery('simple', 'BMW i5')

See:

How to search hyphenated words in PostgreSQL full text search?

Text search is powerful, but you may have to familiarize yourself with it first. The manual provides all you need to know. The index could look like:

CREATE INDEX ON Product USING GIN (to_tsvector('simple', name));

Using Postgres SIMILAR TO with Spring Data to match substring in column

1 Answers1