Search by pattern and operator 'LIKE'

Question

I have a SQL table with a date column (VARCHAR), the date is presented in a table in the date column in two ways:

1.:

05.01.2020

and 2.:

2020-01-05

I request to find / return all rows that are displayed in the 1st way: 05.01.2020

I try to use the 'LIKE' clause this way:

SELECT * FROM     `myTable`
WHERE             `date_column`
LIKE              '%(\d{1,2}).(\d{1,2}).(\d{4})%';

The SQL server performs this query without error, but with zero rows returned. I will probably have a mistake somewhere.

Answer 1

If you actually want to validate the numbers, your regular expression is the better route. However, you need to use the regular expression operator:

WHERE date_column REGEXP '^\d{1,2}[.]\d{1,2}[.]\d{4}$'

Note that . is a wildcard in regular expressions. And based on your question, you want to match the entire string -- not just find the pattern within the string.

If you were to use like, you might as well use:

where date_column like '%.%.%'

This checks that the separators are periods, which is sufficient for distinguishing the two formats.

Answer 2

A simple:

LIKE '__.__.____'

will do for this case.