Geek Answers Handbook

Mysql sql for "replace all but this", where xxx

I have a terrible project database and they used a text box for dates.

So, I need to create a view that has only a year in one column. The problem is that I have dates with any standard format, for example:

  • 01/01/2012
  • 01/01/2012
  • 01 01 2012
  • 1/1/2012
  • 01/2012
  • 1/2012
  • 2012
  • 01,2012

Is there a way to build SQL (MySQL) to get only these 4 year old digits to create a view?

Many thanks for your help!

+4
source share
3 answers

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 is the year, so use

SELECT RIGHT(fieldname, 4) FROM table

will work. If this template does not work, you should either use concat, either start splitting them or write a REGEX statement.

If RIGHT will work, you can do INSERT SELECT

 INSERT INTO table (yearcolumn) SELECT RIGHT(fieldname, 4) FROM table
+4
source

You can use REGEXP to get rows from a given year. However, there is no way to get a capture from a regular expression. However, if part of the year is in the last four digits, use RIGHT() . Otherwise, you will need to reformat the client side.

+1
source

It may also help. This is an Oracle query for the entire current year. It can be easily converted to any SQL. It uses a recursive query to create an annual table. Replace the SYSDATE and Oracle formats with your version of the SQL formats and system date:

 WITH data(r, start_date) AS ( SELECT 1 r, TRUNC(SYSDATE, 'YEAR') start_date FROM dual -- start_date: 1/1/2013 UNION ALL SELECT r+1, TRUNC(SYSDATE, 'YEAR')+r-1 FROM data WHERE r < 365 -- any number: end_date - start_date or get the number of days in your year your way... ) SELECT start_date , TO_CHAR(start_date, 'YYYY') curr_year , TRUNC(start_date, 'IW') wk_starts , TRUNC(start_date, 'IW') + 7 - 1/86400 wk_ends , TO_NUMBER (TO_CHAR (start_date, 'IW')) ISO_wk# FROM data / START_DATE CURR_YEAR WK_STARTS WK_ENDS ISO_WK# ----------------------------------------------------------------------- 1/1/2013 2013 12/31/2012 1/6/2013 11:59:59 PM 1 1/1/2013 2013 12/31/2012 1/6/2013 11:59:59 PM 1 ... 12/28/2013 2013 12/23/2013 12/29/2013 11:59:59 PM 52 12/29/2013 2013 12/23/2013 12/29/2013 11:59:59 PM 52 12/30/2013 2013 12/30/2013 1/5/2014 11:59:59 PM 1
0
source

More articles:


All Articles