I have three tables each with 3 columns, they are identical to each other. Only difference is se_words created with mysql GUI and the other two are created programmatically thrue sql inside mysql GUI.
se_words
en_words
es_words
-> id, name, user
I want to find words through the last 3 chars.
SELECT * FROM `es_words` WHERE word LIKE '%nar' // returns empty results (should be at least 500-1000+)
SELECT * FROM `en_words` WHERE word LIKE '%ate' // returns empty results (should be at least 500-1000+)
SELECT * FROM `se_words` WHERE word LIKE '%ens' // returns 1000+ results
The big problem is that en_words and es_words which are utf8 (same as se_words) always returns empty result.
I have changed it to latin, utf8_bin, utf8_unicode_ci, latin_spanish_ci etc but it doesnt matter. Still gives empty result
but
if I change the sql code to just 1 char and procent sign on both sides, the code gives a result.
SELECT * FROM `es_words` WHERE word LIKE '%a%' // returns 10000+ results
SELECT * FROM `en_words` WHERE word LIKE '%a%' // returns 10000+ results
How come same sqlcode different tables returns different result when all columns has multiply values which should be returned on LIKE search?
edit
Few of the words that should be returned upon LIKE search
es_words
abadernar,abaldonar,abanar,abandonar,abarrenar
en_words
aagate,abacate,abacinate,abalienate,abbate
From the code with trimmed variables
SELECT w.id, w.word, w.user
FROM en_words AS w
WHERE w.word LIKE '%gel' ORDER BY LENGTH(word) ASC LIMIT 0,500
Looks like an \r followed from the dictonary. Could only see it through an SQL DUMP. PHP would not show it at all. I hope it solves the problem for you in the future who encounter it
(943, 'abridgments\r', 0),
(944, 'abrikosov\r', 0),
(945, 'abrikossov\r', 0),
(946, 'abril\r', 0),
(947, 'abrim\r', 0),
(948, 'abrin\r', 0),
(949, 'abris\r', 0),
(950, 'abristle\r', 0),