Inspiring

Question

MySQL String query

Forum|Forum|7 years ago
September 26, 2018
2 replies
726 views

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 has been closed for replies.

BKBK

Community Expert

SELECT id, customerName, orderId

FROM orders

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

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,

^ _ ^

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

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,

^ _ ^

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.

Scanning file for viruses.

This file cannot be downloaded