There is such a line

INSERT INTO BUYINGS_DETAILS ( ORDER_ID, ORDER_DATE, SUPPLIER_ID, PRODUCT_ID, QUANTITY, UNITCOST ) VALUES ( 304, date '9-JUN-2011', 1, 59, 20, 0.84 );

Need to change from 9-JUN-2011 to 2011-06-09. The fact is that there are more than 100k of such lines and each has a different date. Regular expressions failed.

  • one
    show your regex - zhenyab
  • (\ d +) - (\ w +) - (\ d +) and replacement \ 4. \ 3 \ 1 - Vadim
  • With one call I think it will not work. But first you can go through this regular /date '(\d{1})-([AZ]{3})-(\d{4})'/gm with replacement by $ 3- $ 2-0 $ 1, then change all the lines which has two digits in the number, and then replace the months of the words with their digital value using a simple string replacement. - zhenyab
  • Nothing found ( - Vadim
  • Add a program code if there is, how do you start a regular program at all? In what? - zhenyab

2 answers 2

The solution is not universal, however, as an option, you can wrap the date in a regular or own stored function. If you are dealing with MySQL, instead of the date '9-JUN-2011', you can substitute the MySQL function STR_TO_DATE() into the query, which converts a string to a date using the format specified in the second argument of the function

 SELECT STR_TO_DATE('9-JUN-2011', '%d-%b-%Y'); +---------------------------------------+ | STR_TO_DATE('9-JUN-2011', '%d-%b-%Y') | +---------------------------------------+ | 2011-06-09 | +---------------------------------------+

Then you can replace the expression ('\d{1,2}-\w{3}-\d{4}') with STR_TO_DATE(\1, '%d-%b-%Y') . In the case of another database, you should look for an analogue of the function STR_TO_DATE() .

    Found a solution: search (\ d +) - (\ w +) - (\ d +) replacement \ 3- \ 2- \ 1

    • one
      And such a replacement replaces JUN at 06 ? - ReinRaus pm