0

I am using MySQL 8 and the dataset contains these 3 latin accent characters í ñ é. So the thing I am trying to figure out is why am I able to search í é, but not ñ when using the non-accented version of the character. What do I need to do so I can search all those characters by using the non-accent version?

My Collation on the DB/Table/Column is utf8mb4_spanish_ci and Character Set is utf8mb4.

enter image description here

Raymond Holguin
  • 1,050
  • 2
  • 12
  • 35
  • Interesting question. This might be helpful: https://stackoverflow.com/questions/3304464/mysql-diacritic-insensitive-search-spanish-accents – Marshall C Jun 08 '22 at 00:32
  • Please provide `SELECT HEX(place) FROM baptism WHERE ...` so I can see the hex encoding of those letters. Also provide `SHOW CREATE TABLE baptism` so we can tell what collation is involved. – Rick James Jun 20 '22 at 15:20

1 Answers1

1

Well, the reason might be because ñ is not exactly n.

"The character “ñ” is a precomposed character because it is treated as an individual Unicode character and has a Unicode code point of U+00F1. Technically, this character can be decomposed into an equivalent string of a base character “n” (U+006E) and a combining tilde “~” (U+0303)." Reference

There is a way to make ñ recognizable as n by adding COLLATE in the query, such as:

SELECT * FROM baptism WHERE place LIKE '%n%' COLLATE utf8mb4_general_ci;

Try here

FanoFN
  • 6,815
  • 2
  • 13
  • 33