How to extract date from a string column in db2 anything equivalent to patindex

Question

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

Answer 1

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