How do I search for REGEXP based on hexadecimal codes in MYSQL?

Question

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 literally x20

My actual use case is that I want to look for any tags that may have accidentally received control characters.

Answer 1

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 values ​​of characters, not hex. I also could not figure out how to make it find NULL bytes ( \x00 ).

Here is an alternative that uses hexadecimal literals:

 SELECT title FROM tags WHERE title REGEXP CONCAT('[', x'01', '-', x'1F', ']') 

Answer 2

I believe that you do this in the mysql shell, which itself removes backslashes. The following worked for me:

 select id, data regexp '\\x1F\\x8B\\x08' from ...;