How to do a LIKE ignoring accentuation?

Question

I need to make queries in a database of registered users. However, I need this survey to be done by the name of the user, through the LIKE, and this should ignore the encoding UTF-8 present in the names.

What I mean is this::

• When to search c, he must find c and ç.
• When to search a, I want him to find à, ã and á

That is to say,

In doing

SELECT * FROM usuarios where NOME LIKE 'Maria Magalhaes'

He finds the Maria Magalhães

How can I do this in MYSQL?

Answer 1

Man, I’ve been there and solved the problem just by adding collate utf8_general_ci after the like '%string%', in this way:

Select * from TABELA where CAMPO like '%texto_para_encontrar%' collate utf8_general_ci

I didn’t have to make any changes to the BD’s charset or page.

Answer 2

I solved the problem set the tables as utf8_unicode_ci

We convert the character input type of the table

ALTER TABLE `tabela` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Then we convert the data that already exists to the same encoding used in the above Query.

ALTER TABLE `tabela`
CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Answer 3

Credits go to the site SQLFROMHELL https://sqlfromhell.wordpress.com/2010/05/01/ignorando-letras-maiusculas-minusculas-acentos-e-c/

--Quando os textos precisam ser idênticos em nível de minúsculas/maiúsculas, podemos utilizar os COLLATEs com CS:
SELECT
CASE WHEN 'Produção' = 'Produção' COLLATE SQL_Latin1_General_CP1_CS_AS
THEN 1
ELSE 0 END

--Para ignorar a diferença entre as letras maiúsculas e minúsculas, utilizamos COLLATEs com CI:
SELECT
CASE WHEN 'Produção' = 'produção' COLLATE SQL_Latin1_General_CP1_CI_AS
THEN 1
ELSE 0 END

--Quando precisamos ignorar acentos, COLLATES com AI (insensibilidade a acentos):
SELECT
CASE WHEN 'Produção' = 'produçao' COLLATE SQL_Latin1_General_CP1_CI_AI
THEN 1
ELSE 0 END

--Quando precisamos ignorar a diferença até do ç e c, recomendo a utilização de COLLATEs com CI e AI do sistema operacional, exemplo:
SELECT
CASE WHEN 'Produção' = 'producao' COLLATE Latin1_General_CI_AI
THEN 1
ELSE 0 END

Answer 4

create or alter function alfaNum (texto varchar(100)) returns varchar(100)
as
  declare variable stAux varchar(100) = '';
BEGIN
  stAux = upper(:texto);
  stAux = replace(stAux, 'Á', 'A');
  stAux = replace(stAux, 'À', 'A');
  stAux = replace(stAux, 'Ã', 'A');
  stAux = replace(stAux, 'Â', 'A');
  stAux = replace(stAux, 'Ä', 'A');
  stAux = replace(stAux, 'É', 'E');
  stAux = replace(stAux, 'Ê', 'E');
  stAux = replace(stAux, 'Ë', 'E');
  stAux = replace(stAux, 'Ï', 'I');
  stAux = replace(stAux, 'Í', 'I');
  stAux = replace(stAux, 'Î', 'I');
  stAux = replace(stAux, 'Ö', 'O');
  stAux = replace(stAux, 'Ó', 'O');
  stAux = replace(stAux, 'Õ', 'O');
  stAux = replace(stAux, 'Ô', 'O');
  stAux = replace(stAux, 'Ò', 'O');
  stAux = replace(stAux, 'Ú', 'U');
  stAux = replace(stAux, 'Ü', 'U');
  stAux = replace(stAux, 'Ç', 'C');
  stAux = replace(stAux, '&', 'E');
  stAux = replace(stAux, '´', ' ');
  stAux = replace(stAux, '^', ' ');
  stAux = replace(stAux, '~', ' ');
  stAux = replace(stAux, 'Æ', 'A');
  stAux = replace(stAux, 'Ñ', 'N');
  stAux = replace(stAux, 'Ý', 'Y');
  stAux = replace(stAux, 'ª', 'A');
  stAux = replace(stAux, 'º', 'O');
  RETURN Upper(stAux);
END

Answer 5

This approach worked for me:

const pg = require('pg');

const client = new pg.Client({
  connectionString: `postgres://${process.env.PGROOTUSER}:${process.env.PGROOTPASSWORD}@${process.env.PGHOST}:${process.env.PGPORT}/postgres`,
});
await client.connect();

// make sure your postgresql database has the "unaccent" installed
await client.query(`CREATE EXTENSION  IF NOT EXISTS unaccent;`)

// so you can run a query like this:
await models.City.findOne({
  where: {
    countryId: 'BR',
    districtId: 'RS',
    $and: Sequelize.where(
      Sequelize.fn('unaccent', Sequelize.col(`"City"."name"`)),
      'SAO SEBASTIAO DO CAI',
    ),
  },
});

// in the database city name is 'SÃO SEBASTIÃO DO CAÍ' and it will find it