Oracle REGEXP_SUBSTR from CLOB

user_14692474 0
Viewed 1610

I'm trying to find a substring from a CLOB-field in my database.

Consider the following string:

someothertext 2. Grad Dekubitus (Druckgeschwür) mit Abschürfung/Blase/Hautverlust someothertext

I only want to extract the "2. Grad" from the string, but my Regexp doesn't seem to work - I tested it on the string in some online regexp checkers, where it does actually work (Fiddle)

This is my regular expression:

REGEXP_SUBSTR(DBMS_LOB.SUBSTR(cf.TEXT, 4000), '\b[0-9]\.\sGrad$') AS "Grad"

Currently, it returns NULL, but I'm not sure why.

Any ideas on how to get this working?

oracle sql regex
2 Answers

Oracle does not support word boundaries \b in regular expressions.

Either remove the \b or replace it with (^|\s) if you are expecting white space before the digit.

You also need to remove the trailing $ as you are not trying to match the end of the string at that point.

REGEXP_SUBSTR( DBMS_LOB.SUBSTR(cf.TEXT, 4000), '(^|\s)[0-9]\.\sGrad' ) AS "Grad"

Also, if you can have multi-digit numbers then you may want to use [0-9]+.

If you do not want the leading white space then you can wrap the second part of your expression in a capturing group and then extract that capturing group's value with the 6th argument of REGEXP_SUBSTR:

REGEXP_SUBSTR(
  DBMS_LOB.SUBSTR(cf.TEXT, 4000),
  '(^|\s)([0-9]\.\sGrad)',
  1,    -- Start from the 1st character
  1,    -- Find the 1st occurrence
  NULL, -- No flags
  2     -- Return the 2nd capturing group
) AS "Grad"
user_15092640

Oracle regex does not support word boundaries. Also, the $ is redundant in your pattern (note you do not use it in your regex demo).

You can use

REGEXP_SUBSTR(
    'someothertext 2. Grad Dekubitus (Druckgeschwür) mit Abschürfung/Blase/Hautverlust someothertext', 
    '(^|\D)([0-9]\.\sGrad)', 1, 1, NULL, 2
) AS "Grad"

where

  • (^|\D) - Group 1: start of string or a non-digit
  • ([0-9]\.\sGrad) - Group 2: a digit, a dot, as whitespace and Grad

If the digit matched with [0-9] should be preceded with whitespace, you may replace (^|\D) with (\s|^).

user_38329700
Related