CODEWITHSUNDEEP
sqloracle-databasebracketsregexp-substr
MichalAndrzej
MichalAndrzej

Reputation: 77

REGEXP_SUBSTR - how to "avoid" bracket in string

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ł

Upvotes: 1

Views: 304

Answers (3)

Gary_W
Gary_W

Reputation: 10360

Why worry about escaping parens at all? Don't you just want what's after "Form:"? Note that REGEXP_REPLACE will return the original string if a match is not found, where REGEXP_SUBSTR will return NULL.

WITH pqb(description) AS (
  SELECT 'Blanket By-Laws Coverage (Building Only) - Form:G00150' FROM dual
)
--SELECT REGEXP_SUBSTR(description, '.*Form:(.*)', 1, 1, NULL, 1) AS Form
SELECT REGEXP_REPLACE(description, '.*Form:(.*)', '\1') AS Form
from pqb;


FORM  
------
G00150
1 row selected.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

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

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

Use REGEXP_SUBSTR with a capture group around only the form value:

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

Note that the parentheses around (Building Only) are literal, and, as such, they should be escaped with backslash. Also note that in your current query you have another capture group around everything except for the form and value. You don't need that capture group either, so I have removed it.

Upvotes: 2

Related Questions