How to convert different string format into date in oracle

Question

Consider table1 and table2.

table1 is having Column1 with value 'some_string Date: 00:30 05/16/2018 '

table2 is having Column2 with value 'some_string Date: 07-JUN-2018 '

Now I need to update both table1 and table2 where requirement is to first find the date from given string value and then convert that date into format 'HH24:MI:ss MM/DD/YYYY'

Here, problem is need to update value on different servers where String value may have date in different format. I tried to wrote above query but it is not working.

update table2
set column2 = 'some_string Date: '
            || to_char ((SELECT SUBSTR(column2, INSTR(column2, ':') + 1) AS date_value
                         FROM table2),
                      'HH24:MI:ss MM/DD/YYYY')
            || ' ';

Never mind the manipulation of the string value. At a higher level, you want to update table2 and on the right-hand side you use a SELECT from table2 with no filters. That SELECT will return as many rows as table2 has; how is Oracle supposed to use the output of that SELECT to update a single value? You are clearly missing some kind of correlation there. You need to fix that before you do anything else. — mathguy
Slightly related, not quite a duplicate: stackoverflow.com/questions/52187763/… — Sam M

MT0 MT0 · Accepted Answer · 2018-09-06T07:42:20

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table1 ( column1 VARCHAR2(100) );

INSERT INTO table1 VALUES ( 'some_string Date: 00:30 05/16/2018 ' );

Query 1:

MERGE INTO table1 dst
USING (
  WITH split ( rid, prefix, dt ) AS (
    SELECT ROWID,
           SUBSTR( column1, 1, INSTR( column1, 'Date: ' ) + 5 ),
           SUBSTR( column1, INSTR( column1, 'Date: ' ) + 6 )
    FROM   table1
    WHERE  INSTR( column1, 'Date: ' ) > 0
  ),
  converted_dts ( rid, prefix, dt ) AS (
    SELECT rid,
           prefix,
           CASE
           WHEN REGEXP_LIKE( dt, '^\s*\d?\d:\d\d \d?\d([/-])\d?\d\1\d{1,4}\s*$' )
           THEN TO_DATE( dt, 'HH24:MI MM/DD/YYYY' )
           WHEN REGEXP_LIKE( dt, '^\s*\d?\d([\-])(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NO|DEC)\1\d{1-4})\s*%' )
           THEN TO_DATE( dt, 'DD-MON-YYYY' )
           END
    FROM   split
  )
  SELECT rid,
         prefix,
         TO_CHAR( dt, 'HH24:MI:ss MM/DD/YYYY' ) AS dt
  FROM   converted_dts
  WHERE  dt IS NOT NULL
) src
ON ( dst.ROWID = src.RID )
WHEN MATCHED THEN
  UPDATE
  SET column1 = prefix || dt

Results:

Query 2:

SELECT *
FROM   table1

Results:

|                               COLUMN1 |
|---------------------------------------|
| some_string Date: 00:30:00 05/16/2018 |

How to convert different string format into date in oracle

1 Answers