Filter in Delphi does not work with accent

Question

While searching:

AQuery.Filter := 'nome LIKE ''%jose%''';

The result José da Silva is not shown due to the accent in the letter is.

I researched several related issues and realized that:

1. The Mysql call works normal when using my database manager.

SELECT * FROM pessoas
WHERE nome LIKE '%jose%'

This command returns to me as expected José da Silva.

2. Making the call with SELECT in Delphi works.

AQuery.Open('SELECT * FROM pessoas WHERE nome LIKE ''%jose%''');

This command returns to me as expected José da Silva.

3. Making the call with Filter in Delhi NAY works

Filter := 'nome LIKE ''%jose%''';

This command NAY return me the José da Silva.

Problem

I just don’t get what I want when I use the Filter, then there is some problem in this method.

Solutions

I tried several solutions, among them the command.

Filter := 'Upper(nome_pessoa) like '+QuotedStr('%'+ AnsiUpperCase(Texto)+'%');

But I was unsuccessful.

Answer 1

To solve this problem, create a function that removes the special characters as accentuation, and put it like this:

'Upper(nome_pessoa) like '+QuotedStr('%'+ AnsiUpperCase(Substitui(Texto))+'%')

where replaces will be the function to replace special characters for common.

Here’s an example of a function that does this: Function replaces character!

Answer 2

In this case, you only need to modify the collation of the database.

Something like:

ALTER DATABASE "nome_banco_dados" CHARSET = Latin1 COLLATE = latin1_swedish_ci;

1. latin1_general_ci: No distinction between capital letters and lowercase. Searching for "test", records such as "Test" or "TEST" will be returned.
2. latin1_general_cs: It distinguishes between upper and lower case letters. Searching for "test" will only return "test". Options such as "Test" and "TEST" will not be returned.
3. latin1_swedish_ci: Does not distinguish between lower and upper case letters and or accented characters with cedilla, that is, the record that contains the word "intuition" will be returned when there is a search by the word "intúicao".