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.

Start from the right-hand side of the string. Looks like the two rightmost digits are the days, and the two digits to the left of that are the months, and the remaining digits to the left (whether it is one digit, or two, three, or however many) are the years. — John Gordon
– John Gordon, Commented Mar 17 at 1:25

Tim Biegeleisen · Accepted Answer · 2025-03-17 01:07:26Z

3

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;

answered Mar 17 at 1:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manngo Mar 17 at 2:12

I had no idea that extract() would work with durations as well, since it just looked like a number to me. Here’s the reference: ibm.com/docs/en/db2/11.5?topic=functions-extract . Thanks.

Mark Barinstein Mar 17 at 18:43

This doesn't work on a little bit older db2 as described here.

Mark Barinstein · Accepted Answer · 2025-03-17 18:47:58Z

1

A solution with just a number of simple math expressions.

SELECT 
  age
, int(age)/10000 AS yyyy
, (int(age) - int(age)/10000*10000)/100 AS mm
, int(age) % 100 AS dd
FROM (VALUES current_date - '1943-02-25') T(age)

The result is:

AGE	YYYY	MM	DD
820020	82	0	20

fiddle

answered Mar 17 at 18:47

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Collectives™ on Stack Overflow

Extracting a variable part of a string

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related