Skip to Main Content
  • Questions
  • Problems in LOWER() INSIDE REPLACE()

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rajesh.

Asked: August 29, 2016 - 10:03 pm UTC

Last updated: August 30, 2016 - 3:06 am UTC

Version: 11G

Viewed 1000+ times

You Asked

Hi Tom,

I have executed below statements.

create table details
(email varchar2(100));

insert into details
values('a@gmail.com');

insert into details
values('b@gmail.com');

insert into details
values('a@gmail.com,b@gmail.com,c@hotmail.com');

update details
set email = replace(lower(email),'a@gmail.com','AA@yahoo.com');

after that i have executed below query:

select email, replace(lower(email),'b@gmail.com','BB@gmail.com') from details;

Result:


EMAIL RESULT
AA@yahoo.com aa@yahoo.com
b@gmail.com BB@gmail.com
AA@yahoo.com,b@gmail.com,c@hotmail.com aa@yahoo.com,BB@gmail.com,c@hotmail.com


Here we are replacing b@gmail.com to BB@gmail.com but why the first record is converting to lower case??

Thanks in advance..



and Connor said...

SQL> select email, replace(lower(email),'b@gmail.com','BB@gmail.com') x from details;

EMAIL                                              X
-------------------------------------------------- -------------------------------------------
AA@yahoo.com                                       aa@yahoo.com
b@gmail.com                                        BB@gmail.com
AA@yahoo.com,b@gmail.com,c@hotmail.com             aa@yahoo.com,BB@gmail.com,c@hotmail.com



says

1) take the email value "AA@yahoo.com"
2) convert it to lower case "lower(email)"
3) now replace any occurrences of 'b@gmail.com' with 'BB@gmail.com' (and none were found to replace)

So you *still* did (2) above.

Hope this helps.

Is this answer out of date? If it is, please let us know via a Comment