lower function inside replace() in Oracle SQL Problem

I am searching for a pattern "hello world" and replaced with Hello Bob.

Here I want to match all patterns of hello world. The problem is that we cannot match both patterns in replace function. so I make it lower and replacing.

As a Result , It making the whole string as lowercase. "this is Hello Bob program!. this is Hello Bob program"

But I want it as "This is Hello Bob Program!. This is Hello Bob Program"

This is the query: select replace(lower('This is Hello World Program!. This is hello world Program'),lower('Hello world'),'Hello Bob') from dual;

Any suggestion please

Solution

You are receiving your result in all lower case because you are performing the LOWER function on your source string. Instead of using REPLACE, you can use REGEXP_REPLACE since that function has the ability to ignore case sensitivity.

SELECT REGEXP_REPLACE ('This is  Hello World Program!. This is hello world Program',
                       'Hello world',
                       'Hello Bob',
                       1,
                       0,
                       'i')
  FROM DUAL;