sqlregexpostgresqlregex-group

How to find second match in sql

Query

select (regexp_matches('Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane', '\b(\d+)%'))[2]

returns no rows.

Regex testing https://regex101.com/r/nVP3Wg/1 returns 3 rows as expected.

How to get second match value ?

Using Postgres 16

Solution

  • Use regexp_matches function with with ordinality and g flag.

    select r[1] second_match 
from regexp_matches('Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane', 
                    '\y(\d+)%',
                    'g'
  ) with ordinality as t(r, o)
where o = 2;

    (and \y anchor metacharacter instead of \b)