How to replace a substring of a string before a specific character?

Question

Email table:

values:

 [e-mail] [e-mail] [e-mail] 

I want to replace the line before @ with test .

Result:

 [e-mail] [e-mail] [e-mail] 

How to use substring and substitution based on character in string?

Answer 1

You don't even need to use substring or replace , you can use this:

 SELECT 'test' + RIGHT(email, charindex('@', REVERSE(email))) FROM YourTable 

You can check it as follows:

 DECLARE @email nvarchar(50) SET @email = ' [e-mail] ' PRINT 'test' + RIGHT(@email, charindex('@', REVERSE(@email))) 

Answer 2

 declare @t table(email varchar(30)) insert @t values(' [e-mail] '), (' [e-mail] '), (' [e-mail] ') select stuff(email, 1, charindex('@', email), ' Test@ ') from @t 

Result:

 [e-mail] [e-mail] [e-mail] 

Answer 3

You can use SUBSTRING and CHARINDEX :

 UPDATE Email set email = 'test' + SUBSTRING(email, CHARINDEX('@',email), LEN(email)) 

Fiddle: http://sqlfiddle.com/#!3/0face/6/0

Answer 4

You could

 select 'test' + substring(fld, charindex('@', fld), len(fld))