Home / Questions / 5438
Accepted
RubenQs
Asked: 2020-03-09 13:06:42 +0800 CST 2020-03-09 13:06:42 +0800 CST

PL/SQL character replacement

  • 772

I need to replace a particular character from each in oracle.

For example:

cadena= '12121113'

and what I need is to change the "1" to a "2" but only the "1" that is in position 3.

and return me the modified result.

As a result it should remain '12221113'.

I hope I have explained myself well.

oracle

3 Answers

  1. Best Answer

    With this query it will look at the third "case when" value is equal to 1.

    SUBSTR functions

    If the result is true, then the first part of the substring is concatenated, then the part to be changed is concatenated, and then it is concatenated with the end of its string.

    If false, then true will be returned.

    select 
case when substr('12121113', 3, 1) = 1
  then substr('12121113', 1, 2) || replace(substr('12121113', 3, 1), '1', '2') || substr('12121113', 4)
  else '12121113'
end as modified
from dual
    • 1

  2. As an alternative I leave you the following code. you can make use of instr

     instr('12121113','1',2)

    and the query would be the following

    select substr('12121113',1,instr('12121113','1',2)-1)||
    replace(substr('12121113',instr('12121113','1',2),1),'1','2')||
    substr('12121113',instr('12121113','1',2)+1,length(12121113))
from dual
    • 0

  3. Reuben

    This way is shorter than the previous ones, but the key is in the regular expressions, I'm sure there must be an even shorter solution with a single regular expression.

    SELECT 
SUBSTR(REGEXP_REPLACE('12121113','1','2',3,1),1,3) || SUBSTR('12121113',4)
FROM DUAL;

    Result: 12221113

    • 0