REGEXP Oracle SQL

Question

I am having a clob field rq_dev_comments which should replace the username with "anonymous"

Update <TABLE>.req 
   Set rq_dev_comments = regexp_REPLACE(rq_dev_comments, 
   '\<[bB]\>.*gt;,', '<b>anonymous ') 
   where length(rq_dev_comments) > ...

Now my question is, if there is a way to check before wheather "anonymous" is already set or not and how to reduce the datasets?

Example:

rq_dev_comments = "<html>HendrikHeim: I found an error....</html>"

Desired: "<html>Anonymous: I found an error....</html>"

Edit your question and provide sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Oct 5, 2016 at 13:00
Yes - use dbms_lob.instr() to check whether 'anonymous' is already a substring of rq_dev_comments in the where clause. However, this will NOT catch cases where some but not ALL occurrences of "username" were replaced with "anonymous". — user5683823
– user5683823, Commented Oct 5, 2016 at 13:02
Note - the Example doesn't make sense; there is no bold font in a CLOB value. Rather, escape html characters so that the value you show in the example is exactly the text in the CLOB column. — user5683823
– user5683823, Commented Oct 5, 2016 at 13:04
editted, the clob contains html tags @mathguy can you give me an example, im not so deep into plsql — Hendrik Breezy
– Hendrik Breezy, Commented Oct 5, 2016 at 13:05
Are you certain that the only bolded text will be user names, and not also other text fragments? This has nothing to do with the question you asked, but you should make sure you don't change the comments themselves! — user5683823
– user5683823, Commented Oct 5, 2016 at 13:13

user5683823 · Accepted Answer · 2016-10-05 13:11:35Z

1

The following solution will not catch cases where "username" may appear more than once, and some but not all occurrences have already been replaced with "anonymous". So think twice before you use it. (The same would apply to ANY solutions along the lines of what you asked!)

Add the following to your WHERE clause:

... where length(...) ....
      and dbms_lob.instr(rq_dev_comments, '<b>Anonymous') = 0

"= 0" means the search pattern wasn't found in the input string.

Another thing: In the example you show "anonymous" capitalized (with upper case A), but in your code you have it all lower case. Decide one way or another and be consistent. Good luck!

answered Oct 5, 2016 at 13:11

user5683823

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

Collectives™ on Stack Overflow

REGEXP Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related