MySQL REGEXP No Whitespaces No Numbers

Question

I'm trying to return only those rows which colA and colB do not contain a number or a whitespace

Here is my code so far.

SELECT * FROM table_name WHERE colA REGEXP '[^0-9^\W]' AND colB REGEXP '[^0-9^\W]'

given the dataset

   colA        colB
---------- ----------
     test |  testB
      33  |  text    <- this is not returned
     blah |  0123A   <- this is returned

I am assuming my issue is with my regexp... any help please?

Answer 1

Well your expression does not work because: 33 is both numbers and you're looking for any non decimal, non whitespace character. so it doesn't include that row in result.

Try:

SELECT * FROM table_name WHERE colA NOT REGEXP '[0-9[:space:]]' AND colB NOT REGEXP '[0-9[:space:]]'

ETA: Yea forgot that \whatever does not work

Answer 2

MySQL doesn't support the \whatever type escapes for the most part. If you want whitespace, you need to use [[:space:]], which is POSIX syntax. Details on the regex man page here.