MySQL RegEx search each column

Question

I'm searching my MySQL Database with Regex like this:

$search looks like this WORD1|WORD2|WORD3

$regexp = "REGEXP '[[:<:]][[:alpha:]]*($search)[[:alpha:]]*[[:>:]]'";

SELECT * FROM
    zitate
WHERE
    UPPER(`field1`) $regexp OR UPPER(`field2`) $regexp OR UPPER(`field3`)

Now I'm getting, as expected, every entry where $search matches field1 or field2 or field3

But I only want to recieve the entries where WORD1, WORD2 and WORD3 match within each column, not within the whole table.

I know this is because of the OR in my query but is there something better than writing something like this:

WHERE
    UPPER(`field1`) $regexp AND UPPER(`field2`) $regexp
OR
    UPPER(`field2`) $regexp AND UPPER(`field3`) $regexp
OR
    UPPER(`field1`) $regexp AND UPPER(`field3`) $regexp
OR
    UPPER(`field1`) $regexp AND UPPER(`field2`) $regexp AND UPPER(`field3`) $regexp

Or should I use this AND, OR query?

Thanks for any tips and answers!

Really nobody who can give me any advice?

Fabian
– Fabian

2012-03-06 13:22:23 +00:00
Commented Mar 6, 2012 at 13:22 — Fabian
– Fabian, Commented Mar 6, 2012 at 13:22

Community · Accepted Answer · 2017-05-23 11:55:53Z

1

Idea: in MySQL logical values are actually maps to 0 and 1, so instead of ORing them, you could simply add them like:

WHERE 1 < UPPER(`field1`) $regexp
        + UPPER(`field2`) $regexp
        + UPPER(`field3`) $regexp

Alternatively, you should learn about MySQL's full text indexing/searching features, or find some other solution, like Fulltext Search with InnoDB .

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Mar 6, 2012 at 13:52

biziclop

14.6k3 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL RegEx search each column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related