2

I've been trying to write a simple keyword comparison query in mysql but for some reason an all numeric keyword won't match an all numeric search term.

I have a table of products, a table of keywords and joining table. To make things simpler I'm using a view which performs the join, giving me a simple list of keyword strings per product id. 1 product in particular has 4 keywords, "John", "Deere", "8000", "Midroller" and these show up correctly in the view and when joined.

Where I am having trouble is that (keyword = "8000") is never true for the "8000" keyword.

Example

SELECT a.id, kw.keyword AS keyword, (kw.keyword = 'John' OR 
  kw.keyword = 'Deere' OR 
  kw.keyword = "8000" OR 
  kw.keyword = 'Midroller') AS matched
FROM `kc5n2_product` AS a
INNER JOIN `product_keywords` AS kw ON a.id = kw.product
WHERE a.id =119

returns 

 id    keyword    matches
119    8000       0
119    John       1
119    Deere      1
119    Midroller  1

I've had a look at this: MySql: Compare 2 strings which are numbers? but it didn't really help.

I also found this MySQL Query doesn't seem to be outputting expectations but I'm trying trying to compare them as strings, not as numbers.

Even trying

SELECT a.id, kw.keyword AS keyword, (CAST(kw.keyword as Char(4)) = CAST("8000" as Char(4))) AS matched

to try to force mysql to interpret them both as strings didn't help at all.

I found that it will match the search term if I use 

kw.keyword LIKE "%8000%"

but I'd prefer to avoid LIKE if I can as at this stage I would like to keep the search fairly restrictive.

Is there something obvious I am doing wrong?

Community
  • 1
  • 1
DigitalArchitect
  • 63
  • 6
  • It seems like you may have leading/trailing spaces in a row containing `8000` – peterm Aug 23 '13 at 04:08
  • Exactly right! The extra spaces didn't show up in phpmyadmin so I didn't even think to check it. It's a bit frustrating that it was something so basic. – DigitalArchitect Aug 23 '13 at 04:20

2 Answers2

3

In cases like this it usually means that your "misbehaving" value has leading/trailing spaces in it.

You can find all these "bad" records with a query like this

SELECT *
  FROM keywords
 WHERE CHAR_LENGTH(keyword) <> CHAR_LENGTH(TRIM(keyword))

Here is SQLFiddle demo

And you can obviously fix them with a simple update like this

UPDATE keywords
   SET keyword = TRIM(keyword)
 WHERE CHAR_LENGTH(keyword) <> CHAR_LENGTH(TRIM(keyword))

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
  • With select CHAR_LENGTH(files.name) FROM files where id=56 or id=55; I get 23 and 22. the first one has a trailing space. But when I try your search with <> nothing returns, Same if I use !=. – KingAndrew Aug 27 '20 at 00:41
0

I don't see anything wrong in what you have posted. I setup a test case:

CREATE TABLE product (id INT);
CREATE TABLE product_keywords (product INT, keyword VARCHAR(32));

INSERT INTO product
  VALUES (19),(20);
INSERT INTO product_keywords 
  VALUES (19,'Foo'),(19,'John'),(19,'Deere'),(19,'8000'),(19,'Midroller');

SELECT a.id
     , kw.keyword AS keyword
     , ( kw.keyword = 'John' OR 
         kw.keyword = 'Deere' OR 
         kw.keyword = "8000" OR 
         kw.keyword = 'Midroller'
       ) AS matched
  FROM `product` a 
  JOIN `product_keywords` kw 
    ON a.id = kw.product
 WHERE a.id = 19

    id  keyword    matched  
------  ---------  -------
    19  Foo              0
    19  John             1
    19  Deere            1
    19  8000             1
    19  Midroller        1

it looks good to me. (I'd use single quotes around the 8000 literal rather than double quotes, but in default MySQL configuration, it doesn't make a difference.)

Perhaps its a problem in your view query.

What does this query return?

SELECT kw.* FROM product_keywords kw WHERE kw.product = 119 ORDER BY kw.keyword ;
spencer7593
  • 106,611
  • 15
  • 112
  • 140