• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

MySQL String query

Participant ,
Sep 26, 2018 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

548

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
community guidelines
LEGEND ,
Sep 27, 2018 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,

^ _ ^

Votes

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
community guidelines
Community Expert ,
Sep 27, 2018 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

Votes

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
community guidelines
LEGEND ,
Sep 27, 2018 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,

^ _ ^

Votes

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
community guidelines
Community Expert ,
Sep 27, 2018 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

Votes

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
community guidelines
Community Expert ,
Sep 27, 2018 Sep 27, 2018

Copy link to clipboard

Copied

LATEST

SELECT id, customerName, orderId

FROM orders

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

Votes

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
community guidelines
Resources
Documentation