Oracle SQL replace Character

Question

I have a Column in which I would like to replace a certain character, the problem is this character can be in the middle of the string or at the end. And I only want to replace this character and not all the other ones.

For example

Root#root#abc#test#stest#s#beta#402
Root#root#abc#test#stest#s

Now I want to replace the s with an uppercase S but only the s after the 5.#:

Root#root#abc#test#stest#S#beta#402
Root#root#abc#test#stest#S

The structure of the column is always like this, the character to replace is always after the 5th #. Replace wont work bc if I search for #s then the #stest would get #Stest.

thx

Aleksej · Accepted Answer · 2016-05-12 09:12:31Z

3

If you have a fixed character to replace ( 's' in your example) you can use this:

with test(string) as ( select 'Root#root#abc#test#stest#s#beta#402' from dual)
select regexp_replace(string, '(.*)#(.*)#(.*)#(.*)#(.*)#s', '\1#\2#\3#\4#\5#S')
from test

This cuts the string in 5 blocks with and ending '#' and then replaces the 's' after the 5th block with its 'S'.

You can even use regexp to count the occurrences for you:

select regexp_replace(string, '(([^#]*#){5,5})s', '\1S')
from test

This counts exactly 5 occurrences of the block, without need to write it 5 times.

With a different approach, without regexp, you can try:

select substr(string, 1, instr(string, '#', 1, 5) ) ||
       upper(substr(string, instr(string, '#', 1, 5)+1, 1)) ||
       substr(string, instr(string, '#', 1, 5) + 2)
from test

This simply cuts the string in 3 parts (from the begigging to the 5th '#', the following character, the remaining part) and does upper of the character. This can handle different characters, with no need to hardcode 's'

edited May 12, 2016 at 9:12

answered May 12, 2016 at 9:04

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rainer Over a year ago

Thx for your answer, the regexp version was what I was looking for.

Collectives™ on Stack Overflow

Oracle SQL replace Character

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related