Knex Query with Like + '%' is not working

I have this query that have to select all books filtering by a description ignoring uppercase/lowercase.

So I make this query in adonis.js / node.js:

 const queryBook = Book
 queryBook.where('description', 'like', '%'+bookDescription[0]+'%')

I have records with this bookDescription:

"Espanhol for Students ed.1 "

But when I try to filter using only “es” in lowercase, the knex don’t return any record.

When I put “Es”, return the book with the description that I put, so, the like %es% is not working.

I put one debug and I catch this:

knex:query select * from "books" where "description" like ? limit ? undefined +7ms
knex:bindings [ '%es%', 10 ] undefined +6ms

Apparently I don’t find any wrong, but I think the like must return the record in lowercase…

I’m forgetting something?

I’m using postgres.

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.


Interesting. Does Postgres have ILIKE for case-insensitive like?

Smarter every day =)

.whereRaw(`LOWER('description') LIKE LOWER('%?%')`, bookDescription[0])

Or something similar should work too for MariaDB / MySQL. But it seems like it would be really bad performance wise

1 Like

LIKE on mysql/mariadb ignores case normally. If you wan’t to match on case you can use LIKE BINARY

You can try this if it helps:

let keyword = `%${decodeURIComponent(bookDescription[0])}%`

const queryBook = Book
 queryBook.where('description', 'like', 'keyword')

it is not case sensitive

1 Like