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?
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
精彩评论