Menu
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?
637
- a "dot" indicates any character. * - means "0 or more instances of the preceding regex token"
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.
Basic syntax“WHERE fieldname” is the name of the column on which the regular expression is to be performed on. “REGEXP 'pattern'” REGEXP is the regular expression operator and 'pattern' represents the pattern to be matched by REGEXP. RLIKE is the synonym for REGEXP and achieves the same results as REGEXP.
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
66
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.
32
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
