1

Maybe the question was not clear: What makes the difference, based on a character-by-character matching, between the two mail strings provided, as long as both were only [a-z] characters and in the same table (meaning same collation) to explain that some strings fail and some not? Anyone has a clue?

I've found several debates explaining the LIKE and = use in mySql, but not find a fulfilling answer for this issue:

I've found out that searching by whole mailboxes (not truncated, but only complete mailboxes so using no wildcards) using LIKE will not return few of them in my script, but they will match if using = sign instead. (Of course currently function is updated and working properly with equal sign, but I would appreciate if someone could help me bring some light into this).

I can't reproduce the mailboxes per obvious security reasons but, can reproduce the structure of the last one I noticed that fails (each "x" represents a smallcap latin non-special character [a-z]):

select id from table_name where email = "xxx.xxxxxxxxx.xxxxxx@gmail.com"

Returns the id

select id from table_name where email like "xxx.xxxxxxxxx.xxxxxx@gmail.com"

Returns NULL

And this is giving me nuts, because normally it is working fine and, for example, with a structure like this:

select id from table_name where email = "xxxxx.xxxxxxxxx@gmail.com"

Returns the id

select id from table_name where email like "xxxxx.xxxxxxxxx@gmail.com"

Returns the id too

@_@

Maybe there's something wrong with the LIKE matching when there is more than one dot in the mailbox structure? Any other idea?

Thanks in advance for your time fellows.

Aldwoni
  • 1,168
  • 10
  • 24
user3492338
  • 11
  • 3
  • Is it possible there are trailing spaces in some of your data? An "=" comparison on char or varchar would ignore those, while they would be significant in a "LIKE", resulting in no match found. – Joe Stefanelli Nov 10 '15 at 14:57
  • you can't reproduce it. You can't show schema. you can't create a sql fiddle. – Drew Nov 10 '15 at 20:24
  • Thanks for the idea JoeStefanelli. As stated in the examples in my question there are no characters out of smallcaps basic latin or . (dot) and obviously @ because searching in mailboxes field. Drew I'm not trying to create any fiddle, but to learn by understanding. In the question was already stated that (each "x" _[in the emails reported as example]_ represents a smallcap latin non-special character [a-z]). I can add that the Id field is am INT (11) unsigned NOT NULL with AUTO_INCREMENT and that email field is a VARCHAR (100) NOT NULL. Table collation is utf8_general_ci on InnoDB if helps – user3492338 Nov 11 '15 at 10:18

0 Answers0