sqlregexpostgresqlregex-group

How to get second material name from product description


Product description is string like

Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane

Material names are between % and , or up to end of string for last material name Elastane.

Tried to get second material name "Cotton" using

select (regexp_matches('Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane', '%\s(.+),?','g'))[2]

but query does not return any data.

How to get second product name? Using PostgreSql 16


Solution

  • Again, regexp_substr() can handle that:
    demo at db<>fiddle

    select regexp_substr('Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane', 
                        '% ([^,]+),?',1,2,'',1)
    
    regexp_substr
    Cotton

    The parameters after the pattern are the same as in the post in the other thread.