4

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.

  • 3
    SELECT id, HEX(id) FROM table1 WHERE id like 'nm041033%'? – Akina Oct 7 at 5:05
  • @Akina Thanks for the nice insight. HEX() shows me an extra '0A' byte at the end of the string that was not visible. I tried UPDATE 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 at 5:31
  • 2
    I'm surprized. 0x0A at the end of a record value must cause linebreak in output, so the output table on the console become visually broken. You couldn't not see this... – Akina Oct 7 at 5:58
  • I'm ashamed. Using the PHMyAdmin GUI I couldn't note it. I guess using that should be a sin in this exchange xD – Leopoldo Sanczyk Oct 7 at 6:04
  • 1
    My usual 'visual' query to find excess whitespace: select '#' + field + '#' from table - maybe helps someone :) – Arvo Oct 8 at 7:32
4

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...

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....

  • That's true. In this case Is not an open system, just data analysis for science purpose. – Leopoldo Sanczyk Oct 7 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.