Locating letters in a column

netfrugal

I'm restructuring a database column that has letters accidentally entered in a phone number column. The column was structured as a varchar so there could be dashes. But I'm finding that there are some letters accidentally entered into the rows.

Is there a query that I can use that will locate any records that have letters in any rows in the column?

This same query can also work for any other column, like credit card column, or maybe a letters only column - like first and last names.

Anyone did this query yet?

bpat1434

You could use a regular expression:

SELECT * FROM `table` WHERE `phone` REGEXP '[A-Za-z]+'

NogDog

And just to play Devil's Advocate, are you sure all of the letters are unintentional, remembering that most of the numbers on a phone have 3-4 corresponding letters? (In fact, way back when--like when I was a kid--everyone in the US usually referred to the first two numbers of their exchange by letters instead of numbers.)

Sxooter

netfrugal wrote:
I'm restructuring a database column that has letters accidentally entered in a phone number column. The column was structured as a varchar so there could be dashes. But I'm finding that there are some letters accidentally entered into the rows.

Is there a query that I can use that will locate any records that have letters in any rows in the column?

Problems like this are easily solvable by using check constraints. Sadly, MySQL doesn't support them. If it did you could put a check constraint on your column based on a regex and only allow 0-9 and - in the field.

But, you CAN add a check constraint to your application to restrict what you're inserting.