Typeorm: Proper way to make a case-insensitive query on a string column?

[Lugghawk]

I have an entity with a string column, and I'm importing external data into the table. I want to be able to do a case-insensitive query against the column, because it's based on user input.

I'd like to know if there's a way other than normalizing my data in the db to lowercase (which would be inconvenient because I need to display this string back to the user in its original case) and user query.

I'm using a Repository.find({stringColumn: userInput}) to fetch my data.

[pleerock]

I guess you need something like this:

SELECT * FROM `post` WHERE LOWER(`title`) = LOWER("some title")

Can you try to do it following way:

const posts = await repository.createQueryBuilder("post")
     .where("LOWER(post.title) = LOWER(:title)", { title })
     .getMany();

[Lugghawk]

That seems to have worked for me. Thanks.

I was able to omit the alias and just use a query analogous to:

const posts = await repository.createQueryBuilder()
     .where("LOWER(title) = LOWER(:title)", { title })
     .getMany();

As my repository was set up for my specific entity already.

Would there be interest in having a find-option for a case insensitive search?

[pie6k]

How could it be improved to support LIKE operator eg

.where("LOWER(title) LIKE LOWER(%:title%)", { title }) - but it seems not to work

[oxilor]

How could it be improved to support LIKE operator eg

.where("LOWER(title) LIKE LOWER(%:title%)", { title }) - but it seems not to work

.where("LOWER(title) LIKE :title", { title: `%${ title.toLowerCase() }%` })

[Vilx2]

Old issue, but someone just got mislead by it. This solution is fine, if your application just needs to work in one language and this trick works in that language. But there other languages in the world where this doesn't fly. For example, Turkish. The proper way to do case insensitive comparisons is by using collations. However note that the syntax can vary between different RDBMS. Here's how it's done in MySQL:

SELECT * FROM `post` WHERE `title` COLLATE utf8mb4_tr_0900_ai_ci = "some title";

This uses the Turkish rules without case sensitivity AND without accent sensitivity (which is probably what you want most of the time as well).

[tangentlin]

Problem with LOWER() approach on the left side of LIKE would be that it can easily bypass the indexes, leading to lower performance. So one needs to remember to index the relevant column's lower case values.

[osnersanchez]

I am using Repository and in the following way...

repository.findAndCount({ order: { createdAt: "ASC" }, where: { [prop]: Raw(alias =>'LOWER(${alias}) Like '%${keyword.toLowerCase()}%'') } take: take, skip: skip })

I do not know if it's the most efficient way but it works for now for me

[Vilx2]

This does work sometimes, but there are some non-obvious limitations. See
above. As long as those limitations don't apply to you, all is fine. But if
they become relevant at some point, you could be spending days trying to
figure out where the bug in your program is.

On Mon, 8 Jul 2019 at 07:11, osnersanchez [e-mail] wrote:

I am using Repository and in the following way...
repository.findAndCount({ order: { createdAt: "ASC" }, where: { [prop]:
Raw(alias =>'LOWER(${alias}) Like '%${keyword.toLowerCase()}%'') } take:
take, skip: skip })

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/typeorm/typeorm/issues/1231?email_source=notifications&email_token=AE2FGDRXXP65IUSSEUT5ZVLP6K5APA5CNFSM4EFIO5X2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODZL5ETA#issuecomment-509071948,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AE2FGDUZZVGQVCMAYN7CXM3P6K5APANCNFSM4EFIO5XQ
.

[osnersanchez]

Esto funciona a veces, pero hay algunas limitaciones no obvias. Véase más arriba. Mientras esas limitaciones no se apliquen a usted, todo está bien. Pero si se vuelven relevantes en algún momento, podría pasar días tratando de averiguar dónde está el error en su programa.
...
El lunes, 8 de julio de 2019 a las 07:11, osnersanchez @.*> escribió: Estoy usando el repositorio y de la siguiente manera ... repository.findAndCount ({order: {createdAt: "ASC "}, donde: {[prop]: Raw (alias => 'LOWER ($ {alias}) Me gusta'% $ {keyword.toLowerCase ()}% '')} take: take, skip: skip}) - You están recibiendo esto porque usted comentó. Responder a este correo electrónico directamente, visualizarla en GitHub < # 1231 ? Email_source = notificaciones y email_token = AE2FGDRXXP65IUSSEUT5ZVLP6K5APA5CNFSM4EFIO5X2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODZL5ETA # issuecomment-509071948>, o silenciar el hilo < https://github.com/notifications/unsubscribe-auth/AE2FGDUZZVGQVCMAYN7CXM3P6K5APANCNFSM4EFIO5XQ >.

I understand your point, I will make the suggested adjustments to optimize my list by page function, since I have to include Chinese in my languages.

[manuhortet]

For those using postgres, the ILIKE operator perform case insensitive queries:

Entity.find({
    where: `"column" ILIKE 'keyword'` 
});

* Notice I'm using " " to specify the column and ' ' to specify the searched keyword.
Double quotes are used for quoted identifiers and single quotes for string literals

This open issue is about including the ILIKE operator as a typeorm advanced option, which would be the ideal solution for postgres users.

[aaronfulkerson]

Why'd this get closed? Having an Ilike() method exposed would be pretty great.

[Vilx2]

This was closed a long time ago. The Ilike() issue is new. Also, it's pretty tricky to get right, and I suspect there would be many gotchas and caveats for people trying to use it. I'm not saying it's impossible to create something like it, but it definitely won't be easy. Perhaps it would even be too confusing to release.