| Author |
Topic |
|
Blueshadow
Starting Member
7 Posts |
 Posted - 2010-08-12 : 05:46:45
|
| Hi, I have an Employee table with 3 columns as EmpID,EmpName and EmailID in the following fomatEmpID EmpName EmailID10 Jason jason@abc.com11 Robynne robin@bnm.com12 Philip philip@vbn.com13 Sam sam@dsf.com14 Tim tim@dsf.com15 Ken ken@dsf.comI need to change the domain names to test.comas shown below:10 Jason jason@test.com11 Robynne robin@test.com12 Philip philip@test.com13 Sam sam@test.com14 Tim tim@test.com15 Ken ken@test.com |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-12 : 06:03:11
|
declare @email varchar(255)set @email='jason@abc.com'selectleft(@email,charindex('@',@email))+'test.com'
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-12 : 06:06:18
|
You should be using example.com, not test.com (which is a valid domain and will be deluged with emails if any of those addresses actually gets emailed!)You can use something like STUFF(MyColumn, CHARINDEX('@', MyColumn), nnn, '@example.com')I prefer webfred's use of LEFT() |
|
|
|
Blueshadow
Starting Member
7 Posts |
Posted - 2010-08-13 : 05:42:54
|
| Hi, using the following query I can only select the column values with updated email ids likeJason@test.com. DECLARE curEmployee CURSOR FOR SELECT Emailid FROM tbEmp DECLARE @email VarChar(100) OPEN curEmployee FETCH NEXT FROM curEmployee INTO @Email WHILE @@Fetch_Status = 0 BEGIN select left(@email,charindex('@',@email))+'test.com' FETCH NEXT FROM curEmployee INTO @email END CLOSE curEmployeeBut my requirement is to update the table with the email ids like jason@test.com. I am not getting the format as to how to use the functions left,charindex and the cursor with the Update clause, so that all the domain names will be changed to test.com or example.com. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-13 : 07:30:19
|
| Don't need a cursor do you?UPDATE USET Emailid = left(@email,charindex('@', Emailid))+'example.com' FROM tbEmpWHERE Emailid LIKE '%@%' |
|
|
|
Blueshadow
Starting Member
7 Posts |
Posted - 2010-08-13 : 07:40:24
|
| Kristen,How does all emailid's from the table come to the variable @emailid?When I am execting the query you typed I am getting the message as "Must declare the scalar variable @email"When I am declaring the variable @email and executing the whole query as:declare @email varchar(255)UPDATE tbempSET Emailid = left(@email,charindex('@', Emailid))+'example.com' FROM tbEmpWHERE Emailid LIKE '%@%'I am getting all email id columns as NULL |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-13 : 07:53:42
|
quote:
Originally posted by Blueshadow
Kristen,How does all emailid's from the table come to the variable @emailid?When I am execting the query you typed I am getting the message as "Must declare the scalar variable @email"When I am declaring the variable @email and executing the whole query as:declare @email varchar(255)UPDATE tbempSET YourEmailcolumn = left(YourEmailcolumn,charindex('@', YourEmailcolumn))+'example.com' FROM tbEmpWHERE YourEmailcolumn LIKE '%@%'
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Blueshadow
Starting Member
7 Posts |
Posted - 2010-08-13 : 08:19:47
|
| I got it friends...Thanks a ton..Just two lines of code was enoughUPDATE tbempSET Emailid = left(Emailid,charindex('@', Emailid))+'example.com' |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-13 : 09:49:26
|
| You will update columns that have no valid "@" in them. May be zero rows, but its still poor programming style. Every row updated goes into the LOG. If you update rows with no change you are logging them unnecessarily - e,.g. if column contains NULLAlso, if YourEmailcolumn does not contain "@" the LEFT() and concatenation of 'example.com' is going to make a mess of the data. |
|
|
|
|
Changing the domain names in email id to test.com