0

I have a column in a table as history comment where associate manually enters the text along with date in d/mm/yyyy format.

I want to extract the date and store it another column

I did in MSSSQL using substring and patindex

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
Pooja Singh
  • 1

1 Answers1

2
SELECT 
  S
-- Db2 for LUW since 11.1  
--, DATE (TO_DATE (REGEXP_SUBSTR (T.S, '\d{1,2}/\d{1,2}/\d{4}'), 'DD/MM/YYYY')) AS D1
-- Should work on Db2 for Z/OS as well
, DATE (TO_DATE 
( 
  XMLCAST
  (
    XMLQUERY ('fn:replace ($D, ".*?([0-9]{1,2}/[0-9]{1,2}/[0-9]{4}).*", "$1")' PASSING T.S AS "D")
    AS VARCHAR (10)
  )
, 'DD/MM/YYYY'
)) AS D2
FROM
(
          SELECT 'My comment with 31/12/2022 date' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Some date 1/2/2022 here'         FROM SYSIBM.SYSDUMMY1
) T (S)
S D2
My comment with 31/12/2022 date 2022-12-31
Some date 1/2/2022 here 2022-02-01
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
  • It's giving me error of no authorised function named regexp_substr having compatible arguments was found – Pooja Singh May 02 '22 at 10:49
  • It [works](https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=354f1dc5168ee6f85ea41e87d5c4b29f) since Db2 11.1 for LUW. What's your Db2 platform and version? – Mark Barinstein May 02 '22 at 10:52
  • I'm new to DB2, so i tried using some SQL commands but that didn't work for version check but I can see it's z/OS V12 – Pooja Singh May 02 '22 at 11:11
  • I don't have Db2 for Z/OS at hand, but try the updated answer. It works as well on Db2 for LUW. – Mark Barinstein May 02 '22 at 11:53
  • Still giving me error as expression passing to function fn:replace is not valid – Pooja Singh May 02 '22 at 12:11
  • Db2 for Z/OS version of [fn:replace](https://www.ibm.com/docs/en/db2-for-zos/12?topic=functions-fnreplace-function) may not support non-greedy `.*?` part at the beginning of the expression. If your dates are all of exact `DD/MM/YYYY` format, then you may try `.*([0-9]{2}/[0-9]{2}/[0-9]{4}).*` instead. – Mark Barinstein May 02 '22 at 12:59
  • It's still giving me error, well I also tried using Substring (comnt,locate('/',comnt), length (comnt)) but it's giving error of out of range at 2nd 3rd parameter – Pooja Singh May 02 '22 at 13:19