使用regexp从mysql中选择行

Question

I'm trying to select specific rows from my database by using regexp, but it's not working for some reason. What am I doing wrong, and how do I fix it?

select response from allData where response regexp '\*([a-zA-Z]+ )+\*'

It's suppose to select rows like:

*kicks you *

^---With an extra space after the word, and before the asterisk. But it's not working. How do I fix it?

Answer 1

You're not escaping the * correctly. Backslash is both the string and regular expression escape character. In order for the regular expression engine to see the backslash that escapes the *, you have to escape the backslash to get it through the string parser.

select response from allData where response regexp '\\*([a-zA-Z]+ )+\\*'