Extracting a variable part of a string

Question

I have learned that subtracting two dates in Db2 results in a number which is to be interpreted as an interval:

SELECT current_date - '1943-02-25' FROM SYSIBM.DUAL;
--      820020: 82 years, 0 months, 20 days

In this case, I can use substring() and right() to pull it apart:

WITH cte(age) AS (
    SELECT current_date - '1943-02-25' FROM SYSIBM.DUAL
)
SELECT left(age,2) AS yyyy, substr(age,3,2) AS mm, right(age,2) AS dd
FROM cte;

The only problem is if the age is more than 99 years, then it’s the first three or four digits for the years, and the months component starts further down.

If I were to use a regular expression, I would have something like this:

^(\d{2,4})(\d{2})(\d{2})$

I know that Db2 has a regexp_substr() aka regexp_extract() function but I can’t see how the above expression can be used. Everything I’ve read uses a simple expression like o. and nothing with a variable number of characters.

What is the way to extract a variable number of characters for the year and month? Do I use regexp_substr() or something else?

I’m running Db2 12 in a Docker Linux container.

Answer 1

Is there any reason why you can't use the EXTRACT() function here:

WITH cte(age) AS (
    SELECT current_date - '1943-02-25'
    FROM SYSIBM.DUAL
)

SELECT
    EXTRACT(YEAR FROM age) AS yyyy,
    EXTRACT(MONTH FROM age) AS mm,
    EXTRACT(DAY FROM age) AS dd
FROM cte;