Different characters, same ASCII code?

Question

I have this query that throws two results:

SELECT id FROM table1 WHERE id like 'nm041033%'

1. nm0410331
2. nm0410331

And this slightly different query that throws only one result:

SELECT id FROM table1 WHERE id='nm0410331'

1. 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%'

1. nm0410331 49
2. 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.

Answer 1

Thanks to the insight of Akina, who suggested to use HEX() to check the field, I found an extra '0A' byte at the end of one of the values.

After removing the primary key constraing (to avoid the temporary duplicate id), I used: UPDATE table1 SET id = TRIM(TRAILING UNHEX('0A') FROM id); And was able to solve it.

PS: For future googlers, using SELECT id FROM table1 WHERE id like 'nm0410331%' could make me note my silly problem too...

Answer 2

Ideally you shouldn't have to "clean" the data - you should fill them in using TRIM - and using a proper escape-function ... if a linebreak could sneak in there - quite possibly a single quote as well .. and hello, welcome to SQL-injection....