REGEXP not working in mysql

Question

I am trying to find record with names which have non-alpha numeric characters.

I thought that I could do it with REGEXP

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Then I referred another SO question

How can I find non-ASCII characters in MySQL?

I found I could use this query :

SELECT * FROM tableName WHERE NOT columnToCheck REGEXP '[A-Za-z0-9]';

But it returns me zero rows . If I replaced the command to :

SELECT * FROM tableName WHERE columnToCheck REGEXP '[A-Za-z0-9]';

It returns me all the rows!!.

I tried some basic commands :

SELECT 'justffalnums' REGEXP '[[:alnum:]]'; returns 1

which is correct but

SELECT 'justff?alnums ' REGEXP '[[:alnum:]]'; also returns 1

I don't understand why it returs one. It should return 0 as it has space and also a '?' .

Is there anything to be enable in mysql for the regexp to work ?

I am using mysql 5.0 and tried with 5.1 too .

cwallenpoole · Accepted Answer · 2011-07-29 05:03:01Z

4

You need to add ^ (string begins) and $ (string ends) as well as an operator saying a certain number of alphanum's to use. Below I used + which means one or more.

SELECT 'justff?alnums ' REGEXP '^[[:alnum:]]+$';
-- only contains alphanumns => 0

SELECT 'justff?alnums ' REGEXP '^[[:alnum:]]+';
-- just begins with alphanum => 1

SELECT 'justff?alnums ' REGEXP '[[:alnum:]]+$';
-- just ends with alphanum => 0

answered Jul 29, 2011 at 5:03

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

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

1 Comment

KingRider Over a year ago

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '^[[:alnum:]]+$'.

scwagner · Accepted Answer · 2011-07-29 05:03:40Z

0

The regex that you've given does not say that the entire field has to contain the characters in question. You can use the negation character ^ at the beginning of a character set.

SELECT 'justff?alnums' REGEXP '[^A-Za-z0-9]'; returns 1
SELECT 'justffalnums' REGEXP '[^A-Za-z0-9]'; returns 0

answered Jul 29, 2011 at 5:03

scwagner

4,02523 silver badges16 bronze badges

Collectives™ on Stack Overflow

REGEXP not working in mysql

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related