MySQL is there a way to replace utf8 chars in select query?

Question

I have a table with text that certainly will have accents áéíóú etc in the text.

However another system that is connected to this has problems managing accents.

I am creating a View so that this system connects to that view, but I want to remove the accents from the query.

Is there a way to replace those characters on a query? (original data must not be transformed)

Let's say transform:

Héctor
Pablo
Pedro
María

to

Hector
Pablo
Pedro
Maria

Maybe with this solution: https://stackoverflow.com/a/3578644/7948962 I think the MySQL collations are a bit different than SQL server though. — Jacob H, Feb 15 '18 at 18:52
Possible duplicate of [How to remove accents in MySQL?](https://stackoverflow.com/questions/4813620/how-to-remove-accents-in-mysql) — JNevill, Feb 15 '18 at 18:52
@JNevill it's not a duplicate, on that question the user is willing to alter the database — htafoya, Feb 15 '18 at 23:26

score 0 · Answer 1 · answered Feb 17 '18 at 18:37

The standard way of doing this is to apply the Unicode decomposition normalization and then filter out combining characters. MySql doesn't support normalization (or REGEXP_REPLACE or TRANSLATE).

But, if you only have a few composed characters and combining characters that concern you, REPLACE should work well enough.

SELECT 
  data,
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(data, 
    'á', 'a'),
    'é', 'e'),
    'í', 'i'),
    'ó', 'o'),
    'ú', 'u'),
    convert(0xcc81 using utf8), '') -- 'COMBINING ACUTE ACCENT' (U+0301)
FROM test;

SQL Fiddle

@Ggrimaldo not really, there are much more characters that only the accents. Replacing all the characters was not viable. — htafoya, Sep 20 '18 at 15:08

MySQL is there a way to replace utf8 chars in select query?

1 Answers1