Inspiring

Question

MySQL String query

5 replies
756 views
Translate

I have a MySQL query of table column where I want to include ONLY values that have letters and commas.(but does not need to have a comma)

I want to exclude any records that contain any number or special character that is not a comma

The field contains names like this that I want John, Smith or John Smith

But a lot of others are like

John, Smith 1234

John, Smith**

John/Smith

John, Smith 255-4422

that I don't want.

How do I exclude these from a MYSQL query?

mysql

This topic is closed to new replies. Start a new post to keep the conversation going.

BKBK

Community Expert

SELECT id, customerName, orderId

FROM orders

WHERE 1 = (CASE WHEN customerName REGEXP '^[a-zA-Z ]+$' THEN 1 ELSE 0 END)

Translate

WolfShade

Legend

REGEXP in MySQL will match whatever pattern you supply.

SELECT name, address, city, state, zip
FROM tableA 
WHERE name REGEXP '^[A-Za-z, ]$'

HTH,

^ _ ^

Translate

D

Dave Watts

Community Expert

This is a good answer, but remember that this will most likely prevent indexes from working. Usually, if you're doing a comparison against a field in a WHERE clause, you very well might have an index on the field in question.

Dave Watts, Fig Leaf Software

Translate

Dave Watts, Eidolon LLC

WolfShade

Legend

Wasn't aware that this would prevent indexes from working. But, then, I never really understood how indexes work.

V/r,

^ _ ^

Translate

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.