CODEWITHSUNDEEP
javasqlpostgresqlspring-data-jpa
John Smith
John Smith

Reputation: 101

Is it valid to use LIKE with IN in PostgreSQL?

Does it work to use something like

LIKE IN ('%John%', '%Bob%', '%Sean%')

to exclude records which contain these symbols

For example this can be a possible solution but it doesn't work

'John S' or a varchar field NOT LIKE ALL(ARRAY[cast(:listOrArrayOfExcludedNames AS text)])

I've found out that this solution works

'John S' or a varchar field !~ 'John|Bob|Sean'

to exclude records with regex. It's worth mentioning that I use native query.

Anyway, does it work to pass array or list to DATA JPA method to exclude records which contains values from array or list?

Upvotes: 0

Views: 1013

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

This is just invalid syntax:

... LIKE IN ('%John%', '%Bob%', '%Sean%')

This would be valid:

... LIKE ANY ('{%John%, %Bob%, %Sean%}')

But the regular expression operator ~ or its negation !~ is simpler for the use case.

To exclude all rows where varchar_field contains any of the given strings:

... WHERE varchar_field !~ ALL ('{John,Bob,Sean}')

Or:

... WHERE varchar_field !~ ALL (:array_of_strings)

A Postgres array literal looks like this:

'{John,Bob,Sean}'

Note that a null value in varchar_field does not qualify because the expression evaluates to null in this case, and only true passes a WHERE condition.

Unlike LIKE, the regular expression operators don't require leading and trailing wildcards for this (non-)match. See:

Upvotes: 1

Related Questions