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?

Igoris · Accepted Answer · 2011-08-08 21:20:48Z

9

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

edited Aug 8, 2011 at 21:20

answered Aug 8, 2011 at 21:15

Igoris

1,6481 gold badge22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rlemon Over a year ago

Ok so how would I then filter out anything that contains a number.. and 'just a space'. Basically I have a data import that contains a lot of invalid values. (names that contain numbers, or are missing completely) I only want to return valid names. so IF val DOES NOT CONTAIN A NUMBER AND IS NOT BLANK (OR JUST WHITESPACE) note the columns are 'NOT NULL' flagged. So I cannot check for a null value.

rlemon Over a year ago

Note: I re-evaluated the scope, you were correct with this answer.

rlemon Over a year ago

Ok, so WHERE colA LIKE "" worked for removing the blank cells (which are not null)

Abu Bakar Siddik Over a year ago

This work fine WHERE colA not REGEXP '^[0-9]+';

Marc B · Accepted Answer · 2011-08-08 21:16:22Z

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.

answered Aug 8, 2011 at 21:16

Marc B

362k44 gold badges433 silver badges508 bronze badges

Comments

Imre L · Accepted Answer · 2011-08-08 21:38:32Z

1

id suggest to use negate instead: not regexp '[0-9[:space:]]'

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

edited Aug 8, 2011 at 21:38

answered Aug 8, 2011 at 21:15

Imre L

6,29126 silver badges32 bronze badges

Comments

carlosfigueira · Accepted Answer · 2012-11-29 03:37:49Z

1

Another regex you can try is the following: regexp '[^0-9\s]'

edited Nov 29, 2012 at 3:37

carlosfigueira

87.7k14 gold badges137 silver badges176 bronze badges

answered Nov 29, 2012 at 3:18

Jack Liu

192 bronze badges

Comments

Nicola Cossu · Accepted Answer · 2011-08-08 21:05:05Z

0

Not tested but give it a try

regexp '^[^0-9 ]+$'

answered Aug 8, 2011 at 21:05

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

Collectives™ on Stack Overflow

MySQL REGEXP No Whitespaces No Numbers

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related