Regex to get nth occurrence of pattern (Trino)

user_13388557 0
Viewed 364
WITH t(x,y) AS (
    
    VALUES 
    (1,'[2]'),
    (2,'[1, 2]'),
    (3,'[2, 1]'),
    (4,'[3, 2, 5]'),
    (5,'[3, 2, 5, 2, 4]'),
    (6,'[3, 2, 2, 0, 4]')

)

--- my wrong answer below

SELECT

REGEXP_EXTRACT(y, '(\d+,\s)?(2)(,\s\d+)?') AS _1st,
REGEXP_EXTRACT(y,'(.*?(2)){1}.*?(\d+,\s(2)(,\s\d+)?)',3) AS _2nd,
REGEXP_EXTRACT(y,'(.*?(2)){2}.*?(\d+,\s(2)(,\s\d+)?)',3) AS _3rd

FROM t


Expected ans:
| x |        y        |   1st   |   2nd   |   nth   |
| - | --------------- | ------- | ------- | ------- |
| 1 | [2]             | 2       |         |         |
| 2 | [1, 2]          | 1, 2    |         |         |
| 3 | [2, 1]          | 2, 1    |         |         |
| 4 | [3, 2, 5]       | 3, 2, 5 |         |         |
| 5 | [3, 2, 5, 2, 4] | 3, 2, 5 | 5, 2, 4 |         |
| 6 | [3, 2, 2, 0, 4] | 3, 2, 2 | 2, 2, 0 |         |

Need help on the Regex for REGEXP_EXTRACT function in Presto to get the nth occurrence of number '2' and include the figures before and after it (if any)

Additional info:

  • The figures in column y are not necessary single digit.
  • Orders of the numbers are important
  • 1st, 2nd, 3rd refers to the nth occurrence of the number that I am seeking
  • Will be looking for a list of numbers, not just 2. Using 2 for illustration purpose.
trino sql json regex
1 Answers

Must it be a regular-expression?

If you see the text (VARCHAR) [1,2,3] as array-representation (JSON or internal data-type Array), you have more functions available to solve your task.

See related functions supported by Presto:

I would recommend to cast it as array of integers: CAST('[1,23,456]' AS ARRAY(INTEGER))

Finding the n-th occurrence

From Array functions, array_position(x, element, instance) → bigint to find the n-th occurrence:

If instance > 0, returns the position of the instance-th occurrence of the element in array x.

If instance < 0, returns the position of the instance-to-last occurrence of the element in array x.

If no matching element instance is found, 0 is returned.

Example:

SELECT CAST('[1,2,23,2,456]' AS ARRAY(INTEGER));
SELECT array_position(2, CAST('[1,2,23,2,456]' AS ARRAY(INTEGER)), 1); -- found in position 2

Now use the found position to build your slice (relatively from that).

Slicing and extracting sub-arrays

  1. either parse it as JSON to a JSON-array. Then use a JSON-path to slice (extract a sub-array) as desired: Array slice operator in JSON-path: [start, stop, step]

  2. or cast it as Array and then use slice(x, start, length) → array

Subsets array x starting from index start (or starting from the end if start is negative) with a length of length.

Examples:

SELECT json_extract(json_parse('[1,2,3]'), '$[-2, -1]');  -- the last two elements

SELECT slice(CAST('[1,23,456]' AS ARRAY(INTEGER)), -2, 2); -- [23, 456]
user_57302790
Related