CODEWITHSUNDEEP
postgresqlkotlinkotlin-exposed
Jebin
Jebin

Reputation: 742

PSQL Case insensitive search using Exposed

How can we do case insensitive search using Exposed(Kotlin) on a postgres sql database?

SELECT users.id, users.name, users.created_at, users.updated_at FROM users 
WHERE users.name iLIKE '%aaa%'

There is like operator. I don't see ilike operator. Should I be using lowercase on the query field?

Upvotes: 4

Views: 2216

Answers (1)

Tapac
Tapac

Reputation: 2337

ILIKE is PostgreSQL specific function and doesn't support in Exposed at the moment, but you can define it by your own :

class ILikeOp(expr1: Expression<*>, expr2: Expression<*>) : ComparisonOp(expr1, expr2, "ILIKE")

infix fun<T:String?> ExpressionWithColumnType<T>.ilike(pattern: String): Op<Boolean> = ILikeOp(this, QueryParameter(pattern, columnType))

Upvotes: 11

Related Questions