Select regular expression groups in PostgreSQL

Question

I have to do a select based on a regular expression and return some values of the expression as columns.

I managed to do something similar to this

select
(regexp_matches(value, '...', 'g'))[1] as expressionValue1,
(regexp_matches(value, '...', 'g'))[2] as expressionValue2,
(regexp_matches(value, '...', 'g'))[5] as expressionValue3
from table

I am trying to rewrite the query to something like this

select 
    (resultsq)[1] as expressionValue1,
    (resultsq)[2] as expressionValue2,
    (resultsq)[3] as expressionValue3
from (
    select
    regexp_matches(value, '...', 'g') 
    from table
) resultsq

However, I get a syntax error. I think the result text[] is being transformed to text.

I want to do it this way because I understand that the regular expression is evaluated only one time per tuple and the code is clearer.

The regular expression that I have to apply is relatively long and complex and there are many fields to extract.

404 · Accepted Answer · 2022-04-15 16:48:02Z

1

    (resultsq)[1] as expressionValue1,
    (resultsq)[2] as expressionValue2,
    (resultsq)[3] as expressionValue3 ```

You are referencing the subquery alias resultsq rather than the field name, hence array indexing on it doesn't work. The subquery is returning a set of rows with a field named regexp_matches, so reference that in your statement and it will work.

select 
    (regexp_matches)[1] as expressionValue1,
    (regexp_matches)[2] as expressionValue2,
    (regexp_matches)[3] as expressionValue3

https://www.db-fiddle.com/f/g6zdgdo7Jv6x2boL4NKi9m/0

answered Apr 15, 2022 at 16:48

404

8,7622 gold badges33 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

Select regular expression groups in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related