September 12, 2002 at 4:40 am
I am trying to bulk update the domain in an email address.
The length before the "@" varies and that is my problem.
How do I update this field and only replace what is after the "@".
Thanks for any help
September 12, 2002 at 4:53 am
Look at the charindex function, you can use it to determine the position of the @, then use the left function to pull off the characters before it.
Andy
September 12, 2002 at 8:20 am
Here this might give you some ideas:
Create table #email (
email varchar(100))
insert into #email values ('rrrrrr@something.com')
insert into #email values ('eeeeee@something.com')
insert into #email values ('ppppp@something.com')
insert into #email values ('llll@something.com')
insert into #email values ('aaaaaaaa@something.com')
insert into #email values ('cc@something.com')
insert into #email values ('eeeeeeeeeee@something.com')
declare @position_of_@ int
declare @length_of_email int
select
-- first part of email
substring(email,1,charindex('@',email)- 1),
-- last part of email
substring(email,charindex('@',email) + 1,len(email))
from #email
--update table
update #email
set email = substring(email,1,charindex('@',email)- 1) + '@' + 'newdomain.com'
--display results
select email from #email
drop table #email
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 15, 2002 at 12:26 pm
Gregory and Andy
Thanks.
I was right there but could not see the trees for the forest.
Tried to do an input like I do in Oracle and correct me if I am wrong but you must create a function or run the quey in isqlw using a dos input for the variable
Doug
September 15, 2002 at 3:23 pm
September 15, 2002 at 6:26 pm
Andy
I used the charindex.
Douglas
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply