Home > Net >  MySQL NOT LIKE '_%' not working on Wordpress woocommerce wp_woocommerce_order_itemmeta lar
MySQL NOT LIKE '_%' not working on Wordpress woocommerce wp_woocommerce_order_itemmeta lar

Time:12-08

I'm trying to search a Woocommerce mysql database for values that don't begin with _ however when I use

WHERE meta_key LIKE '_%' 

this works perfectly, however when using

WHERE meta_key NOT LIKE '_%'

this yields 0 results despite executing without any errors.

Screenshot of Database Structure

As you can see this is a typical wordpress database albeit with lots of rows. I also notice that meta_key has a default value of NULL, which makes me wonder if NULL values are causing the issue but surely mySQL would error if this was the case? If this is the case, is there a commonly accepted workaround for this whilst still allowing default of null values?

CodePudding user response:

Use Escape keyword

WHERE meta_key NOT LIKE '#_%' ESCAPE '#';

Please refer https://www.guru99.com/wildcards.html

CodePudding user response:

WHERE meta_key LIKE '_%' does not work perfectly. It will match any string that is one or more characters long since _ is a wildcard that matches exactly one character... any character. Escape the _ like so:

WHERE meta_key LIKE '\_%' 
WHERE meta_key NOT LIKE '\_%'
  • Related