Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

REGEXP - Select only rows that contain letters and full stop

Tags:

mysql

I've been trying to write this query, I need to select the rows where a column has only letters (a-z) and a full stop.

I tried this but it's not working:

SELECT * FROM table WHERE (c1 REGEXP '[^a-zA-Z\.]') = 0

This one would usually work in PHP.

like image 216
Drahcir Avatar asked Sep 15 '12 13:09

Drahcir


1 Answers

Try:

SELECT * FROM table WHERE c1 REGEXP '^[a-zA-Z.]+$'

The anchor ^ and $ ensure that you are matching the entire string and not part of it. Next the character class [a-zA-Z.] matches a single upper/lower case letter or a period. The + is the quantifier for one or more repetitions of the previous sub-regex, so in this case it allows us to match one or more of either a period or a upper/lower case letter.

More info on regex usage in MySQL

like image 116
codaddict Avatar answered Nov 02 '22 02:11

codaddict