CODEWITHSUNDEEP
sqloracle-databasereplacestring-matchingregexp-replace
ABY
ABY

Reputation: 393

How to replace only certain part of the text in Oracle?

I am trying to replace a certain words in the text. But don't want the word to be replaced if it's a part of a url. For example: the word technical. It replaced in both the places(including url).

select regexp_replace('Part of the Technical Network Group https://technical.com/sites/',
                      'technical','tech',1,0,'i')
  from dual

Output:

Part of the tech Network Group https://tech.com/sites/

Expected Output:

Part of the tech Network Group https://technical.com/sites/

Upvotes: 3

Views: 1690

Answers (2)

Gary_W
Gary_W

Reputation: 10360

This method uses one regexp_replace() call. It matches the word 'technical' (case-insensitive) only when it is preceded by the start of the line or a space, and saves that preceding character. It gets replaced with the saved character (referenced by '\1') followed by 'tech'.

with tbl(str) as (
  select 'Technical Part of the Technical Network Group https://technical.com/sites/' from dual
)
select regexp_replace(str, '(^| )technical', '\1tech', 1, 0, 'i') fixed
from tbl;

FIXED                                                           
----------------------------------------------------------------
tech Part of the tech Network Group https://technical.com/sites/

Upvotes: 0

Arif Sher Khan
Arif Sher Khan

Reputation: 585

Do it in 2 steps.

First, if word be present in between words, second if the word is first word of sentence.

select 
      regexp_replace(regexp_replace('technical Part of Technical Group https://technical.com/sites/',
                     ' technical', 
                     ' tech',1,0,'i'), 'technical ', 'tech ',1,0,'i') 
  from dual

Hope this will solve your issue.

Upvotes: 2

Related Questions