1

I'm making a serial system for my site, and it using the MySQL CHAR_LENGTH function to get all the serials with the matched length. Thing is, it's returning strings that are more than I'm looking for. I've also tried with CHARACTER_LENGTH and LENGTH, having no impact on the result :/.

This is my I/O:

SELECT serials.code
FROM serials
WHERE CHARACTER_LENGTH(code) = 12

These are the results:

niels-er-sej
KUVX-21-40
KUVX-21-40

As you can see, the last two, are only 10 characters, yet they show up when I search for 12?

I've added a picture of the Character lengths as request: enter image description here

Jazerix
  • 4,729
  • 10
  • 39
  • 71
  • what's the datatype of `code`? Also, try `CHARACTER_LENGTH(TRIM(code))` – Sebas Nov 02 '13 at 02:10
  • Yes, along with @Sebas, I wonder if there is extra whitespace. – Paul Draper Nov 02 '13 at 02:11
  • Hi Sebas :) the datatype is varchar and utf8_general_ci and let me give it a try ^^ – Jazerix Nov 02 '13 at 02:11
  • Well, TRIM didn't work. There are no whitespaces :) I read somewhere that LENGTH counts the bytes, could this be the problem? and if so, would would the solution be? :) – Jazerix Nov 02 '13 at 02:13
  • `CHARACTER_LENGTH` counts the characters. I think there are extra characters in your string, such as carriage returns (\n\r) – Sebas Nov 02 '13 at 02:16
  • I can assure you there isn't :/ – Jazerix Nov 02 '13 at 02:18
  • On your system, do a `SELECT CHARACTER_LENGTH("KUVX-21-40")` it should return 10. If so then the problem is in your serials.code table. – Sébastien Nov 02 '13 at 02:19
  • It does indeed return 10 :), what could the problem be? – Jazerix Nov 02 '13 at 02:20
  • Well now we know that CHARACTER_LENGTH() works properly. Do the same test on the actual content of your table `SELECT serials.code, CHARACTER_LENGTH(serials.code) AS code_length FROM serials` Add a LIMIT clause if you have a lot of data. – Sébastien Nov 02 '13 at 02:26
  • Added a picture with results above – Jazerix Nov 02 '13 at 02:29
  • 3
    How about select * from serials where code = 'KUVX-21-40' if you get no results then you have hidden characters on those values. – xQbert Nov 02 '13 at 02:29
  • You're right, how do I find the hidden character then :P? – Jazerix Nov 02 '13 at 02:32
  • how about ASCII(replace(code,'KUVX-21-40','')) should give you the ascii code of the hidden character once you know it replace it and find the next one. – xQbert Nov 02 '13 at 02:36
  • All I can see from your table data is that only uppercase values are off, and they are always off by 2. Also they have couponid = 10 whereas lowercase rows have couponid = 0. – Sébastien Nov 02 '13 at 02:38
  • On my site, I can upload a csv file with the serials. I checked the csv file, to see the binary data: this is what it returned: 0000-0010: 55 45 58 41-32 38 36 2d-31 32 37 33-0d 0a 55 41 UEXA286- 1273..UA 0000-0020: 4a 46 41 55-2d 32 38 31-37 0d 0a 4b-55 56 58 2d JFAU-281 7..KUVX- 0000-0030: 32 31 2d 34-30 0d 0a 36-36 2d 31 37-2d 48 48 58 21-40..6 6-17-HHX 0000-0032: 0d 0a as you can see, it's adding two dots at the end of each serial – Jazerix Nov 02 '13 at 02:38
  • Walk through each character of the string in question and see where it starts to go wrong `SELECT SUBSTRING(code, 1, 1) FROM serials WHERE id = 21` (1, 1; 2, 1; ...) Probably a control character like a backspace in the string. – Glenn Nov 02 '13 at 02:40
  • 2
    http://stackoverflow.com/questions/401771/how-can-i-find-non-ascii-characters-in-mysql may help – xQbert Nov 02 '13 at 02:40
  • Thank you :) I will see if I can get it fixed – Jazerix Nov 02 '13 at 02:42
  • Be sure to upvote zende's answer if it helps – xQbert Nov 02 '13 at 02:44

0 Answers0