Mysql convert VARCHAR to date using REGEX

I have a VARCHAR field in mysql with dates separated by commas. Like this:

"10/20/2011,10/21/2011,10/22/2011"

I need to use a WHERE condition like this:

where `date` > '10/10/2011'

So my question is basically how can i use (maybe) regex to retrieve the first date in开发者_运维百科 my field (I only need the first) and apply the where condition to it?

---

This will get only the first part, before the comma , :

SUBSTRING_INDEX( varcharField, ',' , 1)

You then need to convert it into date format:

STR_TO_DATE( SUBSTRING_INDEX(varcharField, ',', 1), '%m/%d/%Y')

---

As you have already been told, storing a comma delimited list is a bad idea. But many times it's not within your job duties or abilities to restructure a table.

I think you should look up doing full-text indexes and matching. This will allow for searching within a field. Sadly only available on MyISAM tables.