CODEWITHSUNDEEP
sqlregexoracle-database
eliatou
eliatou

Reputation: 744

regexp_substr: find the text before a given patern

I want to find the text before a given pattern. My main problem is when my text has many rows.

Here the example:

    SQL> with foo as
  2  (select '1 first test error log blabla ' k from dual
  3  union
  4  select '2 second test
  5  zz error log blablabla ' k from dual
  6  )
  7  SELECT REGEXP_SUBSTR(k,'.*error log',1,1) AS result_
  8         ,k from foo;
RESULT_                               K
------------------------------------- -------------------------------------
1 first test error log                1 first test error log blabla
zz error log                          2 second test
                                      zz error log blablabla

The result is false for the second rows. It should be:

RESULT_                               K
------------------------------------- -------------------------------------
1 first test error log                1 first test error log blabla
2 second test                         2 second test
zz error log                          zz error log blablabla

I run this query on a 11.2 Oracle database

Upvotes: 0

Views: 69

Answers (1)

MT0
MT0

Reputation: 168761

From the Oracle documentation, the 5th argument to REGEXP_SUBSTR is:

match_parameter is a text literal that lets you change the default matching behavior of the function.

Which has the options:

  • 'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, then the period does not match the newline character.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( k ) AS
SELECT '1 first test error log blabla ' FROM DUAL UNION ALL
SELECT '2 second test
zz error log blablabla ' FROM DUAL;

Query 1:

SELECT REGEXP_SUBSTR(k,'.*error log',1,1,'n') AS result_,
       k
FROM   table_name

Results:

|                RESULT_ |                              K |
|------------------------|--------------------------------|
| 1 first test error log | 1 first test error log blabla  |
| 2 second test          | 2 second test                  |
| zz error log           | zz error log blablabla         |

Upvotes: 3

Related Questions