Reputation: 3
I got the following strings:
(1640.31; 08/19/2016; 09/13/2016;); (250000.0; 09/30/2016; 02/17/2018;); (100000.0; 03/12/2018; 12/31/2025;);
Or
(1000000.0; 05/30/2018; 06/03/2028;);
I need to return this second to last date, so in these cases for example 1: 03/12/2018 and example 2: 05/30/2018.
Because there are a lot of string-parts ending with ; I can't figure quite out how I can get the second to last date.
Upvotes: 0
Views: 458
Reputation: 173210
Below example for BigQuery Standard SQL
#standardSQL
WITH `project.dataset.table` AS (
SELECT '(1640.31; 08/19/2016; 09/13/2016;); (250000.0; 09/30/2016; 02/17/2018;); (100000.0; 03/12/2018; 12/31/2025;);' AS str UNION ALL
SELECT '(1000000.0; 05/30/2018; 06/03/2028;);'
)
SELECT ARRAY_REVERSE(REGEXP_EXTRACT_ALL(str, r'\d\d/\d\d/\d\d\d\d'))[SAFE_OFFSET(1)] dt
FROM `project.dataset.table`
with result:
Row dt
1 03/12/2018
2 05/30/2018
note: above assumes that dates are always in mm/dd/yyyy or dd/mm/yyyy format, but can be adjusted if different
Upvotes: 2
Reputation: 1271241
I think this does what you want:
select (select array_agg(val order by o desc limit 2) -- the limit is just for efficiency
from unnest(split(str, ';')) val with offset o
where val like '%/%/%'
)[ordinal(2)] a
from (select '1640.31; 08/19/2016; 09/13/2016;' as str) x;
Note that this also (happens to) work with parentheses, if they are really part of the strings.
Upvotes: 2