CODEWITHSUNDEEP
sqldatabaseoracle-databasereplacelowercase
Yash
Yash

Reputation: 45

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

Upvotes: 0

Views: 719

Answers (1)

EJ Egyed
EJ Egyed

Reputation: 6084

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;

Upvotes: 6

Related Questions