SQL UPDATE : insert new word in the middle of a long string

user_14583083 0
Viewed 42

I have a string value like this :

DOMAIN1:word1,word2,word3#DOMAIN2:word4,word5#

And I want to insert a new word in the end of the DOMAIN1 like this :

DOMAIN1:word1,word2,word3,***newword***#DOMAIN2:word4,word5#

But I don’t know if it’s possible and if yes, can I do in Oracle SQL ?

Thanks everyone !

oracle sql string
1 Answers

One option uses regexp_replace():

regexp_replace(mycol, '#DOMAIN2', ',newword#DOMAIN2')

Or, if you dont want to hardcode "DOMAIN2":

regexp_replace(mycol, '(DOMAIN1[^#]*)', '\1,newword')

Demo on DB Fiddle:

with t as (select 'DOMAIN1:word1,word2,word3#DOMAIN2:word4,word5#' mycol from dual)
select mycol,
    regexp_replace(mycol, '#DOMAIN2', ',newword#DOMAIN2') mycol1,
    regexp_replace(mycol, '(DOMAIN1[^#]*)', '\1,newword') mycol2
from t

MYCOL                                          | MYCOL1                                                 | MYCOL2                                                
:--------------------------------------------- | :----------------------------------------------------- | :-----------------------------------------------------
DOMAIN1:word1,word2,word3#DOMAIN2:word4,word5# | DOMAIN1:word1,word2,word3,newword#DOMAIN2:word4,word5# | DOMAIN1:word1,word2,word3,newword#DOMAIN2:word4,word5#
user_106767160
Related