mysql search string with accents

Question

Navigation

#1 by (0 votes)

0

Hello friends, could you help me? I have to make a query in mysql to look for states but they have to be as they are in the table;

Ciudad de México
Hidalgo
Jalisco
Estado de México
Michoacán de Ocampo
Nuevo León
Querétaro
San Luis Potosí
Sinaloa
Sonora

follow this query:

SELECT * FROM wp_pink_estados where nombre like '%nuevo leon%';
SELECT * FROM wp_pink_estados where nombre = 'nuevo leon';

shows me the record of Nuevo León but should not show it because the accent is missing in ó

also try it this way:

SELECT * FROM wp_pink_estados where nombre = 'nuevo leon' COLLATE utf8_bin;

but it shows me these errors:

Palabra clave no reconocida. (near "COLLATE" at position 58)
Símbolo (token) inesperado. (near "utf8_bin" at position 66)

How could I do it to be the exact search? could you help me greetings

query

asked by skycomputer2 21.11.2018 в 17:33

source

1 answer

score 0 · Answer 1

I have tried the queries and they work perfectly so this:

SELECT * 
FROM wp_pink_estados 
WHERE nombre = 'nuevo león' COLLATE utf8_bin;

Like this one:

SELECT * FROM wp_pink_estados 
WHERE nombre LIKE '%nuevo leon%' COLLATE utf8_bin;

The CREATE TABLE used is this:

CREATE TABLE IF NOT EXISTS wp_pink_estados 
(
    nombre VARCHAR(50) COLLATE utf8_spanish2_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have entered the same data of your question. In both consultations it does not yield data. And if I put it that way, if it brings Nuevo León :

SELECT nombre consulta3
FROM wp_pink_estados 
WHERE nombre = 'nuevo leon';