Mysql - Concat strings with accents cause malformed chars

Question

I have the following table:

CREATE TABLE `providers` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1392 DEFAULT CHARSET=latin1;

I try to contact id and name in a query:

SELECT id, name, CONCAT("[", id, "] ", name) AS value FROM providers;

It works great for most entries. However, when name has characters with accents, I get a malformed string:

+-----+-------------------------+-------------------------+
| id  | name                    | value                   |
+-----+-------------------------+-------------------------+
| 138 | ÁREA TECNOLÓGICA        | [138] �REA TECNOL�GICA  |
+-----+-------------------------+-------------------------+

Is there any way to fix it in the SQL level?

also https://dba.stackexchange.com/questions/197634/how-to-fix-accented-character-substitutions-on-french-text-in-mysql-db — P.Salmon, Dec 16 '20 at 09:21
THanks @P.Salmon, unfortunately changing DB is not a possibility in this project. — guyaloni, Dec 16 '20 at 09:22

score 0 · Answer 1 · edited Dec 16 '20 at 09:22

0

Ok, I found the solution, I leave it here in case it will help somebody:

When you try to concat different types, this is what happens. So the solution is to use casting:

SELECT id, name, CONTACT("[", CONVERT(id, CHAR(8)), "] ", name) AS value 
FROM `providers`;

edited Dec 16 '20 at 09:22

Cid

14,968
4
30
45

answered Dec 16 '20 at 09:20

guyaloni

4,972
5
52
92

'So the solution is to use csting' - csting?? – P.Salmon Dec 16 '20 at 09:22
@P.Salmon casting :) – Cid Dec 16 '20 at 09:22

Mysql - Concat strings with accents cause malformed chars

1 Answers1