Need Help with Query - "This but not that"

[Dirty Butter]

I am trying to find instances where certain keywords are missing in my custom table of Search terms for each product.

For instance, I normally add Carter's,Carters,Carter to the keywords of any item of that brand. And I include puppy,puppies,dog,dogs in any search "index" of that animal.

While doing some testing I realize I have accidentally failed to include the apostrophe version of Carter's on some items. And not all my dogs can be found with a search for puppy.

I need a query of the database that would help me find the items that have XXX in the search terms, but do NOT currently have YYY.

It would be great if that query would also provide an automatic way to add the missing YYY to the current list of keywords, with the appropriate comma.

 

[bsmither]

Here is a query where one word must be in it, and one word must not be in it.

SELECT product_id, seo_meta_keywords  FROM CubeCart_inventory 
WHERE seo_meta_keywords RLIKE "Book" 
AND seo_meta_keywords NOT RLIKE "Book's"

Double-check to see if the apostrophe's are actually ' coded.

 

[Dirty Butter]

I renamed seo_meta_keywords in the inventory table to search_keywords, because the latest commit does not have meta keywords any more. Then I had to make appropriate changes in various code so search_keywords would be the source for search results. It is working as it should. But all the testing it took to get it working revealed these errors in my keyword consistency that I would like to rectify.

Some ARE coded with ' and most are not. Some have upper case Carters, etc., and others have lower case carters, etc. These were added gradually over the course of about 10 years, so it's no wonder there's a lack of total consistency.

So all apostrophe search_keyword entries should be '   ??? Does case matter?

 

[bsmither]

Without having tested it, I am going to say, Yes.