Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
REGEXP in MySQL will match whatever pattern you supply.
SELECT name, address, city, state, zip
FROM tableA
WHERE name REGEXP '^[A-Za-z, ]$'
HTH,
^ _ ^
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Wasn't aware that this would prevent indexes from working. But, then, I never really understood how indexes work.
V/r,
^ _ ^
Copy link to clipboard
Copied
Well I don't really know how indexes work exactly either, in a mathematical sense or "below the abstraction" of SQL so to speak. But basically, they let the database jump directly to entries rather than scanning the contents of each row for matches (a "table scan").
If you have a query that looks at the beginning of an indexed column ...
WHERE field = 'value'
...
WHERE field LIKE 'value%'
... the database can use that against the index to find the correct values. But if you have a query that just looks at text somewhere within the column ...
WHERE field LIKE '%value%'
... the database has to literally read the entries in each row for that column.
I will give you one piece of personal advice - one that I wish I'd taken to heart myself more often. And that is, learn all you can about SQL or whatever data storage interaction mechanism you're using (NoSQL, etc, etc). With that information, you can usually provide more improvement to your applications than with any information about your application layer itself (CFML, etc). And, SQL doesn't really change that much over time and will continue to be valuable throughout your life as a developer. I guess this isn't really intended for you specifically, just for everyone who might read it. (Even for myself later!)
Dave Watts, Fig Leaf Software
Copy link to clipboard
Copied
SELECT id, customerName, orderId
FROM orders
WHERE 1 = (CASE WHEN customerName REGEXP '^[a-zA-Z ]+$' THEN 1 ELSE 0 END)