CODEWITHSUNDEEP
databaseoracle-databaseplsql
Theg8nj
Theg8nj

Reputation: 153

Changing character in a string of characters

I was wondering regarding how to edit the following column that exists in oracle DB

PPPPFPPPPPPPPPPPPPPPPPPPPPPPPFPPPPPPPP

I want to only set the 5th F with P without affecting other structure. I've around 700 records and I want to change that position (5th) on all users to P

I was thinking of PLSQL instead of a query, so could you please advice.

Thanks

Upvotes: 0

Views: 89

Answers (4)

Gary_W
Gary_W

Reputation: 10360

Flip the 5th 'bit' to a 'P' where it's currently an 'F'. 

update table
set column = regexp_replace(column , '^(.{4}).', '\1P')
where regexp_like(column , '^.{4}F');

Upvotes: 0

user5683823
user5683823

Reputation:

The solution below looks for the first five characters at the beginning of the input string. If found, it keeps the first four unchanged and it replaces the fifth with the letter P. Note that if the input string is four characters or less, it is left unchanged. (This includes NULL as the input string, shown in the WITH clause which creates sample strings and also in the output - note that the output has FIVE rows, even though there is nothing visible in the last one.)

with
     test_data ( str ) as (
       select 'ABCDEFGH' from dual union all
       select 'PPPPF'    from dual union all
       select 'PPPPP'    from dual union all
       select '1234'     from dual union all
       select null       from dual
     )
select str, regexp_replace(str, '^(.{4}).', '\1P') as replaced
from   test_data
;

STR       REPLACED
--------  --------
ABCDEFGH  ABCDPFGH
PPPPF     PPPPP
PPPPP     PPPPP
1234      1234


5 rows selected.

Upvotes: 0

Juan Diego Godoy Robles
Juan Diego Godoy Robles

Reputation: 14975

Use REGEXP_REPLACE:

> SELECT REGEXP_REPLACE('PPPPFPPPPPPPPPPPPPPPPPPPPPPPPFPPPPPPPP', '^(\w{4}).(.*)', '\1P\2') AS COL_REGX FROM dual

COL_REGX
--------------------------------------
PPPPPPPPPPPPPPPPPPPPPPPPPPPPPFPPPPPPPP

Upvotes: 1

Christian Palmer
Christian Palmer

Reputation: 1302

Klashxx answer is a good one - REGEXP_REPLACE is the way to go. Old fashioned way built up bit by bit so you can see what's going on : 

WITH 
 test_data (text)
 AS  (SELECT '1234F1234F1234F1234F1234F1234F1234' FROM DUAL
 )
SELECT 
 text
,INSTR(text,'F',1,5)    --fifth occurence
,SUBSTR(text,1,INSTR(text,'F',1,5)-1) --substr up to that point
,SUBSTR(text,1,INSTR(text,'F',1,5)-1)||'P'    --add P
,SUBSTR(text,1,INSTR(text,'F',1,5)-1)||'P'||SUBSTR(text,INSTR(text,'F',1,5)+1)    --add remainder of string
FROM
 test_data
;

So what you're trying to do would be something like

UPDATE <your table>
SET <your column> = SUBSTR(<your column>,1,INSTR(<your column>,'F',1,5)-1)||'P'||SUBSTR(<your column>,INSTR(<your column>,'F',1,5)+1)

..assuming you want to update all rows

Upvotes: 0

Related Questions