mysql regexp for search using alias

Question

I am not very good with regexp so I really would like some help to achieve my goal.
When searching in my db I use an alias for specific keywords.

Here is an example

keyword  tets alias test   (someone have spell wrong then word test)
keyword  b.m.w  alias bmw  (if someone write b.m.w instead of bmw)

etc.

So far if a user searches for "bmw 316" I use LIKE "%bmw%316%" to get the results. Now if the user searches for "b.m.w 316" I must use

"%b.m.w%316%" OR
"%bmw%316%"

because b.m.w has alias bmw.

In the case of 6 words with 2-3 aliases there are too many combinations.
I am trying to achieve it with regexp.
In the scenario above it would be something like (bmw|b.m.w) 316.

How do I solve this problem?

Community · Accepted Answer · 2017-05-23 11:43:19Z

1

You are not looking for REGEXP you are looking for a thing called levenshtein distance

MySQL does not (yet) have native support for this (wonderful) concept, but you can download a UDF here:
http://joshdrew.com/
And here's a list so you've got something to choose from:
http://blog.lolyco.com/sean/2008/08/27/damerau-levenshtein-algorithm-levenshtein-with-transpositions/

You can also write your own function in MySQL, so you don't have to install a UDF.
http://www.supermind.org/blog/927/working-mysql-5-1-levenshtein-stored-procedure

Finally this question might help you out as well:
Implementation of Levenshtein distance for mysql/fuzzy search?

A query for the closest match would look something like:

SELECT * FROM atable a ORDER BY levenshtein(a.field, '$search') ASC LIMIT 10

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Sep 1, 2011 at 11:52

Johan

77.4k28 gold badges205 silver badges346 bronze badges

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

2 Comments

ntan Over a year ago

Thank for the links and theory

Johan Over a year ago

You're welcome, it a hard thing to find if you don't know the term to search for. Luckily google fixes any spelling errors otherwise you'd never find it.

Collectives™ on Stack Overflow

mysql regexp for search using alias

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related