some_expression;
another_expression;
raise_application_error(-20100
, 'Explanation message.');
expression;
The column has the text above. The text contains raise_application_error function call in more than one lines. I want to get the entire call by RegEX.
SELECT
t.name,
regexp_substr(t.TEXT, 'raise_application_error.*' || CHR(10) || '.*', 1, 1, 'm') as text
FROM user_source t
WHERE t.TEXT LIKE '%raise_application_error%';
The SQL code above returns the only first line: 'raise_application_error(-20100' but I want the entire call. I thought that '|| CHR(10)' would solve the problem but it didn't.
NAME | TEXT |
---|---|
TEXT_I_DO_NOT_WANT | raise_application_error(-20100 |
TEXT_I_WANT | raise_application_error(-20100, 'Explanation message.'); |
CodePudding user response:
Don't use regular expressions, just use normal string functions:
SELECT name,
substr(
TEXT,
INSTR(text, 'raise_application_error')
) as text
FROM user_source
WHERE TEXT LIKE '%raise_application_error%';
I thought that '|| CHR(10)' would solve the problem but it didn't.
That is because each row of the USER_SOURCE
table contains a single line so you cannot match multiple lines within a single row because that does not exist. What you need to do is find the first row and get all the subsequent rows.
If you want multiple rows then, from Oracle 12, you can use MATCH_RECOGNIZE
to perform row-by-row matching and find the first matching row and extract the remaining rows and then you can aggregate:
SELECT name,
LISTAGG(
CASE cls
WHEN 'HAS_ERROR'
THEN SUBSTR(text, INSTR(text, 'raise_application_error'))
ELSE text
END
) WITHIN GROUP (ORDER BY line) AS text
FROM user_source
MATCH_RECOGNIZE (
PARTITION BY name
ORDER BY line
MEASURES
MATCH_NUMBER() AS match,
CLASSIFIER() AS cls
ALL ROWS PER MATCH
PATTERN (has_error rest* $)
DEFINE
has_error AS TEXT LIKE '%raise_application_error%'
)
GROUP BY name, match
db<>fiddle here