How would I do the following in mysql?
SELECT * FROM table WHERE search REGEXP '.+season\d+\s?.+' limit 10;
I want to match something like:
"hello this is season1 how are you?"
But not:
"hello this is season1episode1 how are you?
Add a comment
|
2 Answers
You can use the following regular expression since \d and \s are not available on MySQL. You can use character classes instead.
You can replace \d with [[:digit:]] or [0-9] and \s with [[= =]] or [ ].
SELECT * FROM table WHERE search REGEXP '.+season[[:digit:]]+[[= =]].+' LIMIT 10
-- or...
SELECT * FROM table WHERE search REGEXP '.+season[0-9]+[ ].+' LIMIT 10
4 Comments
David542
Thanks -- what's the
[= =] mean?
Sebastian Brosch
@David542 -
[= =] matches space characterDavid542
thanks for this great answer. I've never seen the
[= =] before, just \s or ` . Where does [= =]` come from?
Rick James
I think you are missing a pair of brackets.
Before MySQL 8.0,
REGEXP "season[0-9]+[[:>:]]"
meaning "season", at least one digit, then a word boundary. Note that it will stop with punctuation.
REGEXP "season[0-9]+[^a-zA-Z]"
Might work for you -- it says that it should be followed by a letter.
8.0 changes the word boundary to:
REGEXP "season[0-9]+\b"
(Caveat: the backslash may need to be doubled up.)
