MySQL Forums
Forum List  »  Newbie

Re: REGXP include exclude
Posted by: Rick James
Date: April 08, 2012 08:47AM

To rephrase what Peter says...

Trying to get the right regexp makes your head spin; it makes our heads spin. It would be easier to redesign the table structure, then SQL could do the work without any regexps. This will save you a lot of effort in the long run.

These forums help people
* understand SQL
* discover solutions, if you are early in the learning curve
* learn "best practice"
* get more performance out of MySQL
* get the full potential out of MySQL
* etc

Your question violates virtually every one of those.
* SQL is not a text processing language.
* Normalization is one of the big solutions for many database issues
* Commalists are not "best practice"
* A regexp, if it is even possible, will perform much worse than a normalized solution

I'm sorry if you go away angry. I hope that you, and other readers, will better understand what we do and why we do it, especially when we are blunt in saying "don't do it that way".

Make another table with (at least) 3 columns. It will contain one row for each pair of values. The two fields (GERMAN... and IN...) will be in two columns. A third column will have the id so that it can be JOINed back to your original table. The resulting query will be something like
SELECT DISTINCT x...
FROM orig_table x
JOIN new_table n ON n.id = x.id
WHERE col1 LIKE '%GERMAN%' AND col2 LIKE '%ALLENAMENTO%';

(I suggest LIKE since it is faster than REGEXP, and should suffice for your case.)

Then, if you always GERMAN is always at the start of the string, change to
... col1 LIKE 'GERMAN%' ...
and have
INDEX(col1)
This will make it run much faster. (I will be happy to elaborate.)

Options: ReplyQuote


Subject
Written By
Posted
April 06, 2012 05:33AM
April 06, 2012 04:19PM
April 06, 2012 04:46PM
Re: REGXP include exclude
April 08, 2012 08:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.