Search code examples
sqlsql-servert-sqlsubstringcharindex

MSSQL Replace string with another string after a certain character

I am receiving a email id into my sql procedure.I need to replace the email client with a defined string.

Suppose I receive email id such as abc@gmail.com or pqr@yahoo.com or mnz@hotmail.com, in such cases I need to replace @gmail.com / @yahoo.com with a fixed string.

abc@gmail.com - abc@outlook.com
pqr@yahoo.com - pqr@outlook.com
mnz@hotmail.com - mnz@outlook.com

So the string after @ will be replaced with my defined string irrespective of the data received in procedure.

Tried Replace method but it didn't work. I know that i need to use charindex, substring & left functions to make it work but not getting the right combination.

Solution

  • I would keep it simple here and just use CHARINDEX with LEFT:

    UPDATE yourTable
SET email_id = LEFT(email_id, CHARINDEX('@', email_id) - 1) + 'outlook.com';