How do I find all instances of only capital letters, spaces and numbers in a mysql search?

Question

I have a MySQL database with roughly 70,000 records. I want to be able to search the "Person" table on the "Address1" column for all address that only contain capital letters, spaces, and numbers.

The end goal is to flag any addresses that look like this: 124 DOLPHIN STREET so they can be converted to 124 Dolphin Street.

I tried using the MySQL REGEXP, but it see`ms that it doesn't bother w/ case b/c I get results with lowercase characters in them.

Query:

SELECT * 
FROM `Person` 
WHERE `Address1` 
REGEXP '[A-Z\\s0-9]+';

The coalition of the table and column is: latin1_general_cs

Answer 1

MySQL is, by default, not case sensitive (as I've occasionally had to learn the hard way as you're doing now). You need to use a regular expression without alphabetic characters in it, try .... REGEXP '^[[[:upper:]][[:space:]]0-9]+$'