0

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
John Smith
  • 77
  • 7
  • 1
    I would use `!~ 'John|Bob|Sean'` and write a wrapper method that accepts a collection of name parts and converts them to `"John|Bob|Sean"` which is then passed to a [custom JPA method](https://www.baeldung.com/spring-data-jpa-query) – Bohemian Nov 29 '22 at 08:21
  • *"to exclude records which contain these symbols"* ... contain any of these strings or all of them? – Erwin Brandstetter Nov 29 '22 at 08:34

1 Answers1

1

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228