CODEWITHSUNDEEP
sqlt-sqlreplacesubstring
JJ.
JJ.

Reputation: 9970

How do I replace a substring of a string before a specific character?

Table Email:

Values:

[email protected]
[email protected]
[email protected]

I want to replace the string before @ with test.

Result:

[email protected]
[email protected]
[email protected]

How do I use substringing and replace based on a character in the string?

Upvotes: 7

Views: 18676

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44346

declare @t table(email varchar(30))
insert @t values('[email protected]'),
                ('[email protected]'),
                ('[email protected]') 

select stuff(email, 1, charindex('@', email), 'Test@') 
from @t

Result:

[email protected]
[email protected]
[email protected]

Upvotes: 4

Tim Schmelter
Tim Schmelter

Reputation: 460340

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

Upvotes: 2

Alex K.
Alex K.

Reputation: 175976

You could

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

Upvotes: 1

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32750

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 test it out with this:

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

Upvotes: 7

Related Questions