user55 user55 - 4 months ago 8
SQL Question

get a char index and update field value using obtained index

user123@hotmail.com
user145@yahoo.com


I have a column named as EMAIL which contains above data. Now i want to update it. My requirement is as follow:

user123@gmail.com
user145@gmail.com


i just want to update field value after '@' sign.

Answer

Assuming you want to update every email address to have a Gmail domain, you can use an UPDATE statement without a WHERE clause:

UPDATE yourTable
SET EMAIL = LEFT(EMAIL, CHARINDEX('@', EMAIL)-1) + '@' + 'gmail.com'