how to delete words using REGEXP_REPLACE in Oracle

Question

I need help with REGEXP_REPLACE on an Oracle database. I wanted to cut everything from the following string to the phrase [Teradata Database] and leave the rest, and if [Teradata Database] is not in the string then leave it unchanged.

I have tried to solve this problem in this way, but unfortunately it does not work.

select TRIM(REGEXP_REPLACE('2021.07.29 13:45:36  ERR   GEOSPATIAL_LOCATOR_CBS.sql /mup/projects/IDM IDM   IdmLoadDetailLayer.sql  226   3706 42000 [Teradata Database] [TeraJDBC 17.10.00.14] [Error 3706] [SQLState 42000] IDM_UPSERT_FORMER:Syntax error: expected something between '','' and '',''.', '.*(\[Teradata Database\] : .* [^.]+)')) AS ERROR_MESSAGE from dual;

Correct result:

[Teradata Database] [TeraJDBC 17.10.00.14] [Error 3706] [SQLState 42000] IDM_UPSERT_FORMER:Syntax error: expected something between ',' and ','.

Thanks in advance for your help

Littlefoot · Accepted Answer · 2021-07-29 20:39:29Z

1

You don't need regular expression for that; substr + instr do the job nicely. Sample data till line #4, query begins at line #5.

SQL> with test (col) as
  2    (select q'{2021.07.29 13:45:36  ERR   GEOSPATIAL_LOCATOR_CBS.sql /mup/projects/IDM IDM   IdmLoadDetailLayer.sql  226   3706 4200
0 [Teradata Database] [TeraJDBC 17.10.00.14] [Error 3706] [SQLState 42000] IDM_UPSERT_FORMER:Syntax error: expected something between '
','' and '',''.'}'
  3     from dual
  4    )
  5  select substr(col, instr(col, '[Teradata Database]')) result
  6  from test;

RESULT
----------------------------------------------------------------------------------------------------
[Teradata Database] [TeraJDBC 17.10.00.14] [Error 3706] [SQLState 42000] IDM_UPSERT_FORMER:Syntax er
ror: expected something between '','' and '',''.'


SQL>

edited Jul 29, 2021 at 20:39

answered Jul 29, 2021 at 19:04

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris Maurer · Accepted Answer · 2021-07-29 20:25:19Z

0

Using Regexp_Replace you need to scan the entire string as a match and use a capture group for only that part you wish to retain, like this: ^.*?(mysequence.*)$ Using your [Teradata Database] sequence,

Regexp_Replace(biglongstring, '^.*?(\[Teradata Database\].*)$', '$1')

The ability to specify capture groups makes Regexp_Replace an especially powerful text transformation tool.

answered Jul 29, 2021 at 20:25

Chris Maurer

2,9321 gold badge13 silver badges9 bronze badges

Comments

Barbaros Özhan · Accepted Answer · 2021-08-02 08:14:43Z

Yet you can use REGEXP_REPLACE() function such as

WITH t(error_message,ptr) AS
(
  SELECT '2021.07.29 13:45:36  ERR   GEOSPATIAL_LOCATOR_CBS.sql /mup/projects/IDM IDM   IdmLoadDetailLayer.sql  226   3706 42000 [Teradata Database] [TeraJDBC 17.10.00.14] [Error 3706] [SQLState 42000] IDM_UPSERT_FORMER:Syntax error: expected something between '','' and '',''.',
         '[Teradata Database]' 
    FROM dual
)  
SELECT ptr||REGEXP_REPLACE(error_message,'(.*\'||ptr||')(.*)$','\2') AS extracted_str
  FROM t;

EXTRACTED_STR
------------------------------------------------------------------------------------------------------------------------------------------------
[Teradata Database] [TeraJDBC 17.10.00.14] [Error 3706] [SQLState 42000] IDM_UPSERT_FORMER:Syntax error: expected something between ',' and ','.

Demo

Collectives™ on Stack Overflow

how to delete words using REGEXP_REPLACE in Oracle

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related