REGEXP_REPLACE expression issue

user_12273109 0
Viewed 91

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;
regexp-replace oracle sql regex
1 Answers

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>
user_90979060
Related