CODEWITHSUNDEEP
oracle-databaseconcatenation
MB34
MB34

Reputation: 4404

Oracle concatenate if the column doesn't already contain value

I have the following Oracle code:

update registration set 
    reg_comments=pMessage||decode(reg_reg_int_hld_wait,1,'Was on hold ')||reg_comments
where reg_rid=v_reg_rid;

I want to only concatenate the reg_comments to itself IF it doesn't already contain pMessage.

How would I do that?

Upvotes: 0

Views: 74

Answers (2)

Patrick Bacon
Patrick Bacon

Reputation: 4640

If you only want to perform this update when reg_comments does not contain pMessage, then I would use the instr function which check to see if reg_comments contains pMessage. 

UPDATE registration
SET reg_comments=pMessage
  ||DECODE(reg_reg_int_hld_wait,1,'Was on hold ')
  ||reg_comments
WHERE reg_rid                    =v_reg_rid
AND INSTR(reg_comments, pMessage)= 0 ;

The instr will return a 0 when the substring parameter (in this case, pMessage) does not occur in reg_comments.

~~~~~~Addendum ~~~~~~~~~~~~~~~~~

If more than one column is involved with updating and you truly want to update all columns in your set clause for all records identified in the where clause, I would use Bob's approach.

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Another option:

update registration
  set reg_comments = CASE
                       WHEN INSTR(reg_comments, pMessage) = 0
                         THEN pMessage ||
                                decode(reg_reg_int_hld_wait, 1, 'Was on hold ') ||
                                reg_comments
                       ELSE
                         reg_comments
                     END
where reg_rid = v_reg_rid

Share and enjoy.

Upvotes: 0

Related Questions