Extract word by Regex postgreSQL [duplicate]

Question

a colums include code like 'a357' , 'b123' with many word and i want to extract them it work with

select *, regexp_matches(col1,'a\d{3}') from table

but i also want the 'b123' code then i write this code not work:

select *, regexp_matches(col1,'(a|b)\d{3}') from table

where as (a|b) is regex. Please show me solution or any other way not regexp_matches because i need to trim '{}' sign after that.

@WiktorStribiżew thank a lot, my problem solved, but it closed too soon, select *, substring(campaign_name from '(?:a|b|B|A)\d{2,3}' --'[abAB]\d{2,3}' ) from table — Tom Tom
– Tom Tom, Commented Jun 24, 2020 at 10:39

Gordon Linoff · Accepted Answer · 2020-06-24 10:30:03Z

-1

The issue is the subexpression in parentheses. As the documentation explains:

If a match is found, and the pattern contains parenthesized subexpressions, then the result is a text array whose n'th element is the substring matching the n'th parenthesized subexpression of the pattern.

In your case, this is easily fixed by using a character class:

regexp_matches(col1, '[ab]\d{3}')

answered Jun 24, 2020 at 10:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Tom Tom Over a year ago

thank a lot, my problem solved, but it closed too soon, select *, substring(campaign_name from '(?:a|b|B|A)\d{2,3}' --'[abAB]\d{2,3}' ) from table

Collectives™ on Stack Overflow

Extract word by Regex postgreSQL [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related