Home » SQL & PL/SQL » SQL & PL/SQL » replace function question
replace function question [message #383197] Tue, 27 January 2009 12:20 Go to next message
jjj0923
Messages: 4
Registered: January 2009
Location: pennsylvania
Junior Member
I have a table with a field called email

some of the email fields have username@hotmial.com instead of username@hotmail.com and I would like to search for all addresses containing the string @hotmial.com and change that to @hotmail.com and not disturb the username in from of the @.

can someone give a pointer on how the replace function should be coded up?

thanks in advance.

jeff
Re: replace function question [message #383199 is a reply to message #383197] Tue, 27 January 2009 12:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: replace function question [message #383200 is a reply to message #383197] Tue, 27 January 2009 12:55 Go to previous messageGo to next message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
You can use the following in a simple fashion.

SELECT REPLACE ('username@hotmial.com', '@hotmial.com', '@hotmail.com') mail_id  FROM DUAL;

MAIL_ID             
--------------------
username@hotmail.com

1 row selected.


and for better search and replace you can use REGULAR EXPRESSION, some example can help you

http://www.psoug.org/reference/regexp.html

Thanks
Trivendra
Re: replace function question [message #383201 is a reply to message #383197] Tue, 27 January 2009 13:00 Go to previous messageGo to next message
jjj0923
Messages: 4
Registered: January 2009
Location: pennsylvania
Junior Member
I think I need to clarify something...

the string in front of the @ will be changing constantly, so I need to retain that and simply search for and replace certain strings after the @ sign
Re: replace function question [message #383202 is a reply to message #383197] Tue, 27 January 2009 13:06 Go to previous messageGo to next message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
can you post some DDL and DML for that, hopefully then we can help you...

It will always replace string after @ sign

[Updated on: Tue, 27 January 2009 13:10]

Report message to a moderator

Re: replace function question [message #383272 is a reply to message #383201] Wed, 28 January 2009 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
jjj0923 wrote on Tue, 27 January 2009 20:00
I think I need to clarify something...

the string in front of the @ will be changing constantly, so I need to retain that and simply search for and replace certain strings after the @ sign

In what the solution provided does not work?

Regards
Michel
Re: replace function question [message #383276 is a reply to message #383197] Wed, 28 January 2009 01:17 Go to previous message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Another sample, compare the previous and output strings. Hope that helps.

with data as (select 'wilbert@hotmial.com, theresa@hotmail.com, matet@hotmial.com' e1 from dual)
 select replace (e1,'@hotmial','@hotmail') as result from data

RESULT
wilbert@hotmail.com, theresa@hotmail.com, matet@hotmail.com


EDIT: Typo

Regards,
Wilbert

[Updated on: Wed, 28 January 2009 01:36]

Report message to a moderator

Previous Topic: Issue with UTL_FILE
Next Topic: explain plan concepts
Goto Forum:
  


Current Time: Sat Feb 08 13:17:03 CST 2025