CODEWITHSUNDEEP
mysqlregex
David542
David542

Reputation: 110163

Mysql regex search with digit

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?

Upvotes: 0

Views: 166

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

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

demo on dbfiddle.uk

Upvotes: 2

Rick James
Rick James

Reputation: 142298

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.)

Upvotes: 0

Related Questions