CODEWITHSUNDEEP
mysql
Drahcir
Drahcir

Reputation: 11972

REGEXP - Select only rows that contain letters and full stop

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.

Upvotes: 10

Views: 18133

Answers (1)

codaddict
codaddict

Reputation: 455282

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

Upvotes: 19

Related Questions