CODEWITHSUNDEEP
sqloracle-database
Korki Korkig
Korki Korkig

Reputation: 2814

How to update the first and the last specific word in a string

I have a column named description with the following values: 

'Row first AP form here AR form to all AP was'
'Row second AP form there AR form to all AP was'
'..........................................'

I try to replace the word AP with WP but only the first and the last one in the sentence like this:

'Row first WP form here AR form to all WP was'
'Row second WP form there AR form to all WP was'
'..........................................'

Could you please help me to get the updated data above with a script like this please?:

UPDATE
    SET description = REPLACE(description, ????)
WHERE description = 'Row %% AP form %% AR form to all AP was'

Upvotes: 0

Views: 47

Answers (3)

Popeye
Popeye

Reputation: 35930

You can use the regexp_replace to formulate your final string and use regexp_count to find the records to update as follows:

UPDATE YOUR_TABLE
   SET description = REGEXP_REPLACE(REGEXP_REPLACE(description, 'AP', 'WP', 1, 1), 
                  'AP', 'WP', 1, REGEXP_COUNT(description, ' AP ') - 1)
WHERE REGEXP_COUNT(description, ' AP ') > 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271191

You can use regexp_replace() twice:

select regexp_replace(regexp_replace(str, 'AP', 'WP', 1,
                                     regexp_count(str, 'AP') - 1
                                    ),
                      'AP', 'WP', 1, 1
                     )
from (select 'Row first AP form here AR form to all AP was' as str from dual union all
      select 'Row second AP form there AR form to all AP was' as str from dual
     ) x;

Here is a db<>fiddle.

Upvotes: 1

psaraj12
psaraj12

Reputation: 5072

You can use the below to update your data 

        WITH data
         AS (SELECT * FROM YOUR_TABLE),
         d1
         AS (SELECT stuff,
                    Instr(stuff, ' AP ', 1)  start1,
                    Instr(stuff, ' AP ', -1) end1
             FROM   data),
         d2
         AS (SELECT stuff,
                    Regexp_replace(stuff, '(^.{'
                                          ||start1
                                          ||'})(.{1})(.*)$', '\1W\3') first_done,
                    end1
             FROM   d1)
    SELECT stuff,
           Regexp_replace(first_done, '(^.{'
                                      ||end1
                                      ||'})(.{1})(.*)$', '\1W\3')
    FROM   d2

Upvotes: 1

Related Questions