SQL: find all fields containing non-alpha characters

Forum Moderators: open

Message Too Old, No Replies

SQL: find all fields containing non-alpha characters

I hope this is an easy one

httpwebwitch

3:43 pm on Feb 14, 2008 (gmt 0)

I have about 10,000 rows
I need to select (later, to delete) all the rows that contain characters other than A-Z.

No numbers, no punctuation, no lower case, no accented characters either... the only characters allowed are A-Z

how do I do that?

phranque

11:53 pm on Feb 14, 2008 (gmt 0)

SELECT * FROM table_name WHERE column_name REGEXP '^[A-Z]*$';

if you are using mysql, regexp became case insensitive with V3.23.4 so use "BINARY" on the column to force case sensitivity:
SELECT * FROM table_name WHERE BINARY column_name REGEXP '^[A-Z]*$';

if you want to allow blanks as well, add that to the pattern:
SELECT * FROM table_name WHERE BINARY column_name REGEXP '^[A-Z ]*$';

httpwebwitch

3:03 pm on Feb 15, 2008 (gmt 0)

Regex. I should have known. The BINARY thing was key. Thanks phranque!

'^[A-Z]*$'
chooses fields that contain 0 or more characters A-Z, which matches almost every row in my table.

Instead...
SELECT * FROM dict WHERE BINARY wordnospaces REGEXP '[^A-Z]'
selected all the rows that contained any character that was not A-Z

phranque

3:18 pm on Feb 15, 2008 (gmt 0)

um, that's what i meant to say.
i must have misremembered.
=8)