Mysql SQL for "replace everything but this" where xxx
date, mysql, regex, sql, view
Solution
It really depends on the whole data structure, you can use REGEX or String functions.
For example, with your sample data the last 4 digits on the right are the year so using
SELECT RIGHT(fieldname, 4) FROM table
would work. If that pattern doesn't work then you've either got to use concat and start splitting them or write a REGEX statement.
If RIGHT will work then you can do an INSERT SELECT
INSERT INTO table (yearcolumn)
SELECT RIGHT(fieldname, 4) FROM table
Problem
I've got a terrible database on a project and they've used a text field for dates. So, I need to build a view that has only the year in one column. The problem is that I have dates with any standard format like: - 01-01-2012 - 01.01.2012 - 01 01 2012 - 1/1/2012 - 01/2012 - 1/2012 - 2012 - 01.2012 Is there any way to build an SQL (MySQL) to get only those 4 year digits to build a view? Thanks a lot for your help!