0

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
Improve this question
8
  • Works for you? sqlfiddle.com/#!4/9eecb/3139 Commented Apr 7, 2015 at 0:12
  • Yes, thanks, that works for that example. I've added other examples that this also needs to work for. Commented Apr 7, 2015 at 0:45
  • And when there isn't a _notes_ related? Commented Apr 7, 2015 at 1:37
  • Null preferably. However the current SQL leaves those fields with #, which is fine for my purpose. Commented Apr 7, 2015 at 2:10
  • When there is two ocurrences you want then 2 or just the first? Commented Apr 7, 2015 at 3:56

1 Answer 1

Reset to default
1

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.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.