CODEWITHSUNDEEP
mysqlsqlregexselect
Ifedi Okonkwo
Ifedi Okonkwo

Reputation: 3646

mysql REGEXP to match a string ending in 2 digits that are preceded only by non-digits

I have struggled quite a bit with this.

In MySQL I would like to query a column for strings that end with a double-digit number. I don't care what else comes before, as long as the last characters are two digits preceded by at least one non-digit

For instance, these strings should match:

"Nov. 12th, 60"

"34 Bar 56"

"Foo-BAR-01"

"33-44-55"

"-------88"

"99"

When I do this:

SELECT <string> REGEXP <pattern>

Now, the <pattern> bit is what I need help on.

Thanks.

Upvotes: 2

Views: 10737

Answers (2)

Pedro Lobito
Pedro Lobito

Reputation: 98921

SELECT * FROM mytable WHERE mycolumn REGEXP "^.*[^0-9][0-9]{2}$";

Regex Explanation:

^.*[^0-9][0-9]{2}$

Assert position at the beginning of the string «^»
Match any single character that is NOT a line break character «.*»
   Between zero and unlimited times, as few or as many times as needed to find the longest match in combination with the other quantifiers or alternatives «*»
Match any single character that is NOT present in the list below and that is NOT a line break character «[^0-9]»
   A character in the range between “0” and “9” «0-9»
Match a single character in the range between “0” and “9” «[0-9]{2}»
   Exactly 2 times «{2}»
Assert position at the very end of the string «$»

Upvotes: 5

Casimir et Hippolyte
Casimir et Hippolyte

Reputation: 89557

There is no need to describe all the string, the end should suffice:

SELECT 'Nov. 12th, 60' REGEXP '(^|[^[:digit:]])[[:digit:]]{2}$';
SELECT '99' REGEXP '(^|[^[:digit:]])[[:digit:]]{2}$'

So in short the two digits at the end [[:digit:]]{2}$ are preceded by a non digit character [^[:digit:]], OR | the begining of the string ^.

Note: you can write it in a less posix style, it doesn't change anything (only a little shorter):

SELECT 'Nov. 12th, 60' REGEXP '(^|[^0-9])[0-9]{2}$';

Upvotes: 2

Related Questions