REGEXP_REPLACE expression issue

Question

I got some sql script below to remove word between words, but it will find last occurrence of my (/hide) instead of the first occurrence. Need help to get the output as expected. Thanks.

select regexp_replace('(hide)it(/hide)should be show(hide)my(/hide) text',
       '^\(hide\).*\(/hide\)', '') "TESTING" 
  from dual;

I expect the output will be:

should be show text

but the actual output is:

text

If my data is in one of the column with datatype of clob. As currently i use below script to select. For example my table is testing_table with column of desc_str with data_type of clob which inside contain value '(hide)it(/hide)should be show(hide)my(/hide) text';

select trim(to_char(regexp_replace(desc_str,'^\(hide\).*\(/hide\)',''))) as desc 
  from testing_table 
 where OOE_FP_SS_ID = $id;

I am using the oracle sql developer.

novri
– novri

2019-10-25 09:44:10 +00:00
Commented Oct 25, 2019 at 9:44 — novri
– novri, Commented Oct 25, 2019 at 9:44

Littlefoot · Accepted Answer · 2019-10-29 07:28:18Z

3

Based on your example:

SQL> with test (col) as
  2    (select '(hide)it(/hide)should be show(hide)my(/hide) text' from dual)
  3  select regexp_replace(col, '\(hide\)\w+\(/hide\)', '') result
  4  from test;

RESULT
-------------------
should be show text

SQL>

[EDIT: CLOB]

You asked for a CLOB. Here's an example:

SQL> create table test (desc_str clob);

Table created.

SQL> insert into test values ('(hide)it(/hide)should be show(hide)my(/hide) text');

1 row created.

SQL> select regexp_replace(desc_str, '\(hide\)\w+\(/hide\)', '') result from test;

RESULT
--------------------------------------------------------------------------------
should be show text

SQL>

Another example:

SQL> with test (desc_str) as
  2    (select '(hide)ItemId: 4334(/hide)|(hide)Description Item - SubType:(/hide) Name - Item|(irow)Price Value: MYR 1,668' from dual)
  3  select regexp_replace(desc_str, '\(hide\).+?\(/hide\)', '') result from test;
                                                ^
RESULT                                          | this question mark was missing
------------------------------------------
| Name - Item|(irow)Price Value: MYR 1,668

SQL>

edited Oct 29, 2019 at 7:28

answered Oct 25, 2019 at 9:47

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

11 Comments

novri Over a year ago

How about if my text '(hide)it(/hide)should be show(hide)my(/hide) text' is one of the column in table with data type of clob?

Littlefoot Over a year ago

Try and see what happens.

novri Over a year ago

with test (col) as (select to_char(desc_str) from testing_table where id = 6041) select regexp_replace(col, '(hide)\w+(/hide)', '') result from test;

novri Over a year ago

i try using above code it cannot hide the word between hide

novri Over a year ago

I try above data is working fine, but may i know why the data below unable to hide. (hide)ItemId: 4334(/hide)|(hide)Description Item - SubType:(/hide) Name - Item|(irow)Price Value: MYR 1,668

|

Collectives™ on Stack Overflow

REGEXP_REPLACE expression issue

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related