Search code examples
regexpostgresqlsql-likesorm

Non sensitive search in SORM with PostgreSQL

How to do non sensitive search in database using SORM and PostgreSQL? I tried whereLike:

DB.query[User].whereLike("fullname", "%" + term + "%").fetch()

This performs a case sensitive search. PostreSQL supports ILIKE operator, but didn't find ILIKE function in SORM DSL API.

I tried with whereRegex too:

DB.query[User].whereRegex("fullname", term).fetch()

This returns an error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "REGEXP"

Acording to documentation PostgreSQL doesn't use the REGEXP operator, it uses a ~ (tilda) operator. As I understand this causes an error. I would like to perform something like this with regexp:

SELECT "fullname" FROM "user" WHERE "fullname" ~ 'john';

Thanks!

Solution

  • The regex not working in Postgres was a bug, which is now fixed in release 0.3.17. Thanks for spotting that.

    Concerning the case-sensitiveness, looks like Postgres uses a different operator for regexes as well. Hmmm. Looks like there is no way to do this with Postgres currently. Introduction of appropriate operators will be required for SORM, contribution is welcome.