I have this query that throws two results:
SELECT id FROM table1 WHERE id like 'nm041033%'
- nm0410331
- nm0410331
And this slightly different query that throws only one result:
SELECT id FROM table1 WHERE id='nm0410331'
- nm0410331
I tried to check the ASCII of the last character and got the same:
SELECT id,ascii(substr(id,9,1)) FROM table1 WHERE id like 'nm041033%'
- nm0410331 49
- nm0410331 49
I guess it is a rare encoding problem. How can I solve it?
PS: The field id
is a primary key. The charset is latin1_general_ci
, and the values were inserted using PHP utf8_decode()
.
UPDATE: I changed the charset to ascii_general_ci
, and now this query gives me zero results:
SELECT id FROM table1 WHERE id='nm0410331'
However, those two ids are not the same yet. If I use SELECT DISTINCT
or GROUP BY
I get two rows.
PS: The last character isn't the number you can type with the keyboard.
SELECT id, HEX(id) FROM table1 WHERE id like 'nm041033%'
? – Akina Oct 7 '19 at 5:05HEX()
shows me an extra '0A' byte at the end of the string that was not visible. I triedUPDATE table1 SET id=REPLACE(id,UNHEX('0A'),'');
and didn't work. I will try further. set idpelicula=replace(idpelicula,unhex('0a'),'') – Leopoldo Sanczyk Oct 7 '19 at 5:31select '#' + field + '#' from table
- maybe helps someone :) – Arvo Oct 8 '19 at 7:32