| update using replace function [message #505969] |
Thu, 05 May 2011 15:48  |
jjj0923
Messages: 4
Registered: January 2009
Location: pennsylvania
|
Junior Member |
|
|
I have a table called email that contains a field called email
I have a few records in the table that contain example@hotmial.com and want to change the hotmial to hotmail
I also have some that are line example@hotmail.co and I want to change those to hotmail.com
so - I want to do a substring search and replace.
can the replace do this for me in one select statement - how do I structure the sql to do this?
thanks in advance.
|
|
|
|
|
|
| Re: update using replace function [message #505979 is a reply to message #505977] |
Thu, 05 May 2011 17:02  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
For example:
update tab set col = replace(col,'hotmial.com','hotmail.com')
where col like '%hotmial.com';
replace
For the second case you would have to restrict the update to the rows that end with 'hotmail.co';
Perhaps:
update tab set col = replace(col,'hotmail.co','hotmail.com')
where col like '%hotmail.co';
[Updated on: Thu, 05 May 2011 17:06] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: update using replace function [message #506067 is a reply to message #506010] |
Fri, 06 May 2011 07:01 |
jjj0923
Messages: 4
Registered: January 2009
Location: pennsylvania
|
Junior Member |
|
|
such a friendly group here... excuse me forgetting what I posted two years ago....you people have way too much time on your hands, are incredibly rude or just don't get way from your flat panels very often.
many thanks to the nice person who answered by question - ThomasG - you're kindness is exemplary in a world of mean spirited people.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: update using replace function [message #506167 is a reply to message #506166] |
Fri, 06 May 2011 15:23 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
@yassirrehman: The point is:
- The question was about correcting "@hotmial.com" and "@hotmail.co" specifically.
- Since the poster hasn't managed to figure out how to format a query in two years, it is possible that he will just copy/paste whatever he gets into a possibly existing live system.
So a query that changes EVERY ':alpha:{7}.:alpha:{2,3}' e-Mail to 'hotmail.com' might wreak havoc in that possibly existing live system.
That's why it's not a good idea to post a complete solution that doesn't fit the problem 100% when there is a high probability the original poster doesn't understand it.
|
|
|
|
| Re: update using replace function [message #506169 is a reply to message #505969] |
Fri, 06 May 2011 16:11 |
yassirrehman@gmail.com
Messages: 9
Registered: February 2008
Location: USA
|
Junior Member |
|
|
Thomas,
What makes you think that this query will change every thing to hotmail.com and that this is a 100% solution for what was originally asked.
"One line Solution"
select regexp_replace(account,'@:alpha:{7}.:alpha:{2,3}' , '@hotmail.com') Cadot_req from table1;
Thanks & sensible/positive questions appreciated.
|
|
|
|
|
|
| Re: update using replace function [message #506171 is a reply to message #506170] |
Fri, 06 May 2011 16:40 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
yassirrehman,
Quote:
What makes you think that this query will change every thing?
I'm terribly sorry. I just assumed that you actually posted the actual output of the query you actually ran, where it looked like the query actually worked. As it turns out, it doesn't work. So the original posters data is safe.
SQL>
SQL> CREATE TABLE table1 (account VARCHAR2(100));
Table created.
SQL>
SQL> INSERT INTO table1 VALUES ('example@hotmial.com');
1 row created.
SQL> INSERT INTO table1 VALUES ('abc@hotmale.com');
1 row created.
SQL> INSERT INTO table1 VALUES ('zadsfdf@hotmial.com');
1 row created.
SQL> INSERT INTO table1 VALUES ('Thisoneisforyou@hotmail.co');
1 row created.
SQL>
SQL>
SQL> select regexp_replace(account,'@:alpha:{7}.:alpha:{2,3}' , '@hotmail.com')
2 Cadot_req from table1;
CADOT_REQ
--------------------------------
example@hotmial.com
abc@hotmale.com
zadsfdf@hotmial.com
Thisoneisforyou@hotmail.co
SQL>
I apologize for my mistake, next time I will check if you faked the output first.
[Updated on: Fri, 06 May 2011 16:41] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
