Menu
SELECT title FROM tags WHERE title REGEXP '[\x20]' returns all things with x, 2, or 0;
SELECT title FROM tags WHERE title REGEXP '\x20' returns all things with literally x20
My actual use-case is that I want to search for any tags that may have accidentally gotten control characters in.
666
Basically, LIKE does very simple wildcard matches, and REGEX is capable of very complicated wildcard matches. In fact, regular expressions ( REGEX ) are so capable that they are [1] a whole study in themselves [2] an easy way to introduce very subtle bugs.
MySQL only has one operator that allows you to work with regular expressions. This is the REGEXP operator, which works just like the LIKE operator, except that instead of using the _ and % wildcards, it uses a POSIX Extended Regular Expression (ERE).
There may be a better way to do this, but here is what I came up with:
SELECT title FROM tags WHERE title REGEXP CONCAT('[',CHAR(1),'-',CHAR(31),']')
Note that these are decimal character values, not hex. I also couldn't figure out a way to get it to find NULL bytes (\x00) as well.
Here is an alternative that uses hex literals:
SELECT title FROM tags WHERE title REGEXP CONCAT('[', x'01', '-', x'1F', ']')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
