lower function inside replace() in Oracle SQL Problem

Question

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

EJ Egyed · Accepted Answer · 2022-01-05 15:44:18Z

6

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;

answered Jan 5, 2022 at 15:44

EJ Egyed

6,1341 gold badge11 silver badges25 bronze badges

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

5 Comments

Yash Over a year ago

Can you pls explain, why 1 and 0 are passing there

Littlefoot Over a year ago

Why wouldn't you, @Yash, read documentation EJ posted? It explains everything.

Alex Poole Over a year ago

@Yash - those are the default values for the position and occurrence arguments, as shown in the documentation EJ linked to; as they are defaults they can usually be omitted, but as these are positional parameters you have to supply values for those in order to be able to supply a value for the match_param argument.

EJ Egyed Over a year ago

If you click on the link I had provided, it has the documentation for each of the parameters. 1 is the position of the string to start searching and the 0 is which occurrence to replace. 0 means all occurrences.

Yash Over a year ago

Apologize I missed that.. Thank you all for your responses. I got that. Once again Thank you so much for your time to helping me

Collectives™ on Stack Overflow

lower function inside replace() in Oracle SQL Problem

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related