Highlighted

MySQL String query

Participant ,
Sep 26, 2018

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?

Views

401

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

MySQL String query

Participant ,
Sep 26, 2018

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?

Views

402

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 26, 2018 0
LEGEND ,
Sep 27, 2018

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 27, 2018 2
Adobe Community Professional ,
Sep 27, 2018

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 27, 2018 0
LEGEND ,
Sep 27, 2018

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 27, 2018 0
Adobe Community Professional ,
Sep 27, 2018

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 27, 2018 2
BKBK LATEST
Adobe Community Professional ,
Sep 27, 2018

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)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 27, 2018 1