Venkat Ch Venkat Ch - 3 months ago 16
MySQL Question

MySQL Regular expressions: How to match digits in the string with \d?

I have a column

release_date
which stores date in a string format(Not in DATETIME format. Because they can be sometime any other string literals).

I want to find all the records based on given month and year but with any date.

Tried following but did not work for me,

> Post.find(:all, :conditions => ["release_date RLIKE ? AND deleted_at is null", "^\d{2}-01-2016"])


When I try same thing with following by giving direct date, it works fine.

> Post.find(:all, :conditions => ["release_date RLIKE ? AND deleted_at is null", "09-01-2016"])


Note:- I am using
Ruby On Rails 2
and
MySQL


Your help is much appreciated!

Answer

The \d is not supported by mysql/posix. Use a character class [0-9] and it should work. Alternatively you could use the posix character class for numbers [:digit:].

Demo: http://sqlfiddle.com/#!9/b952d/3

http://www.regular-expressions.info/posixbrackets.html