CODEWITHSUNDEEP
regexplsql
Deamo
Deamo

Reputation: 13

Replace all instances with REGEXP_REPLACE in PLSQL

I've been trying to replace all text surrounding the keyword POSTPONE in a given string using REGEXP_REPLACE in PLSQL.

In instances where there is two POSTPONE notes adjacent to one another, only the frist instance is being replaced.

Below is the example I've been working on

CROWHURA_POSTPONE_26/03/2015_Postpone_1 30/03/2015 00:00:00~CAMERONR_POSTPONE_30/03/2015_Postpone_1 04/04/2015 00:00:00~CAMERONR_NOTES_30/03/2015_waiting on sr results~CHINGD_POSTPONE_04/04/2015_Postpone_1 07/04/2015 00:00:00

I want only the text related to the __NOTE_ 

#CAMERONR_NOTES_30/03/2015_waiting on sr results#

The REGEXP I've been using is this:

REGEXP_REPLACE(q.comments,'(~|^).{0,10}_POSTPONE_.{1,42}(~|$)','# ')

Any help would be much appreciated.

EDIT: Further examples, that the replace needs to work for: For each of these, I want any and all _note_ related text

VANHOOYJ_NOTES_27/03/2015_Site switching out ~VANHOOYJ_POSTPONE_27/03/2015_Postpone_1 03/04/2015 00:00:00
FABISHD_POSTPONE_31/03/2015_Postpone_1 06/04/2015 00:00:00
FABISHD_NOTES_31/03/2015_GE enquiry~FABISHD_POSTPONE_31/03/2015_Postpone_1 06/04/2015 00:00:00
CAMERONR_NOTES_31/03/2015_booked sr ~CAMERONR_POSTPONE_31/03/2015_Postpone_1 05/04/2015 00:00:00
FABISHD_POSTPONE_01/04/2015_Postpone_1 06/04/2015 00:00:00
CHINGD_POSTPONE_01/04/2015_Postpone_1 03/04/2015 00:00:00~CHINGD_POSTPONE_04/04/2015_Postpone_1 07/04/2015 00:00:00
FABISHD_NOTES_31/03/2015_GE enquiry~FABISHD_POSTPONE_31/03/2015_Postpone_1 06/04/2015 00:00:00
CHINGD_NOTES_01/04/2015_waiting on switching~CHINGD_POSTPONE_01/04/2015_Postpone_1 06/04/2015 00:00:00
FABISHD_NOTES_19/03/2015_Needs to be completed by switching~FABISHD_POSTPONE_19/03/2015_Postpone_1 26/03/2015 00:00:00~FABISHD_NOTES_26/03/2015_Switching~FABISHD_POSTPONE_26/03/2015_Postpone_1 30/03/2015 00:00:00~CHINGD_POSTPONE_30/03/2015_Postpone_1 06/04/2015 00:00:00
CHINGD_NOTES_27/03/2015_GEN2360163 to confirm read~CHINGD_POSTPONE_27/03/2015_Postpone_1 01/04/2015 00:00:00~CHINGD_POSTPONE_29/03/2015_Postpone_1 02/04/2015 00:00:00~CHINGD_POSTPONE_04/04/2015_Postpone_1 07/04/2015 00:00:00
CHINGD_POSTPONE_29/03/2015_Postpone_1 02/04/2015 00:00:00~CHINGD_POSTPONE_04/04/2015_Postpone_1 07/04/2015 00:00:00
CHINGD_NOTES_01/04/2015_waiting on switching~CHINGD_POSTPONE_01/04/2015_Postpone_1 06/04/2015 00:00:00
FABISHD_NOTES_30/03/2015_Unsure where to credit units to.~FABISHD_POSTPONE_30/03/2015_Postpone_1 31/03/2015 00:00:00~CAMERONR_POSTPONE_31/03/2015_Postpone_1 05/04/2015 00:00:00

Upvotes: 0

Views: 249

Answers (1)

Noel
Noel

Reputation: 10525

SQL Fiddle

**Query **:

select val,
regexp_replace(val, '(~?([^~]+_NOTES_[^~]+)~?)|(.)','#\2#') x,  --keeps the notes part and replaces everything esle with #
regexp_replace(regexp_replace(val, '(~?([^~]+_NOTES_[^~]+)~?)|(.)','#\2#'),'#{2,}','#') y  --removes consecutive #s
from
test t

Results:

|                                                                                                                                                                                                                                     VAL |                                                                                                                                                                                                                                                                                                                                                                                                                      X |                                                                                                Y |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------|
|                                                                                                                                                                      VANHOOYJ_NOTES_27/03/2015_Site switching out ~VANHOOYJ_POSTPONE_27 |                                                                                                                                                                                                                                                                                         #VANHOOYJ_NOTES_27/03/2015_Site switching out ################################################################################ |                                                  #VANHOOYJ_NOTES_27/03/2015_Site switching out # |
|                                                                                                                                                                                                                     FABISHD_POSTPONE_31 |                                                                                                                                                                                                                                                                                                                                          ############################################################################# |                                                                                                # |
|                                                                                                                                                                                 FABISHD_NOTES_31/03/2015_GE enquiry~FABISHD_POSTPONE_31 |                                                                                                                                                                                                                                                                                                     #FABISHD_NOTES_31/03/2015_GE enquiry############################################################################## |                                                            #FABISHD_NOTES_31/03/2015_GE enquiry# |
|                                                                                                                                                                               CAMERONR_NOTES_31/03/2015_booked sr ~CAMERONR_POSTPONE_31 |                                                                                                                                                                                                                                                                                                  #CAMERONR_NOTES_31/03/2015_booked sr ################################################################################ |                                                           #CAMERONR_NOTES_31/03/2015_booked sr # |
|                                                                                                                                                                                                                     FABISHD_POSTPONE_01 |                                                                                                                                                                                                                                                                                                                                          ############################################################################# |                                                                                                # |
|                                                                                                                                                            CHINGD_POSTPONE_01/04/2015_Postpone_1 03/04/2015 00:00:00~CHINGD_POSTPONE_04 |                                                                                                                                                                                                                        ############################################################################################################################################################################################### |                                                                                                # |
|                                                                                                                                                                                 FABISHD_NOTES_31/03/2015_GE enquiry~FABISHD_POSTPONE_31 |                                                                                                                                                                                                                                                                                                     #FABISHD_NOTES_31/03/2015_GE enquiry############################################################################## |                                                            #FABISHD_NOTES_31/03/2015_GE enquiry# |
|                                                                                                                                                                         CHINGD_NOTES_01/04/2015_waiting on switching~CHINGD_POSTPONE_01 |                                                                                                                                                                                                                                                                                              #CHINGD_NOTES_01/04/2015_waiting on switching############################################################################ |                                                   #CHINGD_NOTES_01/04/2015_waiting on switching# |
| FABISHD_NOTES_19/03/2015_Needs to be completed by switching~FABISHD_POSTPONE_19/03/2015_Postpone_1 26/03/2015 00:00:00~FABISHD_NOTES_26/03/2015_Switching~FABISHD_POSTPONE_26/03/2015_Postpone_1 30/03/2015 00:00:00~CHINGD_POSTPONE_30 | #FABISHD_NOTES_19/03/2015_Needs to be completed by switching######################################################################################################################FABISHD_NOTES_26/03/2015_Switching################################################################################################################################################################################################## | #FABISHD_NOTES_19/03/2015_Needs to be completed by switching#FABISHD_NOTES_26/03/2015_Switching# |
|                                               CHINGD_NOTES_27/03/2015_GEN2360163 to confirm read~CHINGD_POSTPONE_27/03/2015_Postpone_1 01/04/2015 00:00:00~CHINGD_POSTPONE_29/03/2015_Postpone_1 02/04/2015 00:00:00~CHINGD_POSTPONE_04 |                                                #CHINGD_NOTES_27/03/2015_GEN2360163 to confirm read#################################################################################################################################################################################################################################################################################################################### |                                             #CHINGD_NOTES_27/03/2015_GEN2360163 to confirm read# |
|                                                                                                                                                            CHINGD_POSTPONE_29/03/2015_Postpone_1 02/04/2015 00:00:00~CHINGD_POSTPONE_04 |                                                                                                                                                                                                                        ############################################################################################################################################################################################### |                                                                                                # |
|                                                                                                                                                                         CHINGD_NOTES_01/04/2015_waiting on switching~CHINGD_POSTPONE_01 |                                                                                                                                                                                                                                                                                              #CHINGD_NOTES_01/04/2015_waiting on switching############################################################################ |                                                   #CHINGD_NOTES_01/04/2015_waiting on switching# |
|                                                                                               FABISHD_NOTES_30/03/2015_Unsure where to credit units to.~FABISHD_POSTPONE_30/03/2015_Postpone_1 31/03/2015 00:00:00~CAMERONR_POSTPONE_31 |                                                                                                                                                       #FABISHD_NOTES_30/03/2015_Unsure where to credit units to.###################################################################################################################################################################################################### |                                      #FABISHD_NOTES_30/03/2015_Unsure where to credit units to.# |

Thanks to @Jorge Campos for the sqlfiddle.

Upvotes: 1

Related Questions