REGEXP_SUBSTR - how to "avoid" bracket in string

Question

I have below string in db Blanket By-Laws Coverage (Building Only) - Form:G00150 and I need to return only "G00150" value.

I'm using this REGEXP_SUBSTR (pqb.description is above string)

SELECT  MAX(REGEXP_SUBSTR(pqb.description,'(Blanket By-Laws Coverage (Building Only) - Form:)(.*)',1,1,'i',2))    columnname 
FROM    tablename [...]

And I'm getting null value for this, I suppose it's because brackets in string, but I don't have an idea how to avoid it. In 99% of the cases, descriptions do not have brackets, so this REGEXP works, but this is not the case and I cannot find way to make it work

Can anyone help?

Thanks, Michał

Answer 1

You need to escape the brackets and also you need first sub-expression. so use the following regexp

SELECT  MAX(REGEXP_SUBSTR(pqb.description,
                         'Blanket By-Laws Coverage \(Building Only\) - Form:(.*)',
                          1,1,'i',1) )    columnname 
FROM    tablename [...]

db<>fiddle