How do I replace a substring of a string before a specific character?
17,148
Solution 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 test it out with this:
DECLARE @email nvarchar(50)
SET @email = '[email protected]'
PRINT 'test' + RIGHT(@email, charindex('@', REVERSE(@email)))
Solution 2
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]
Solution 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
Solution 4
You could
select 'test' + substring(fld, charindex('@', fld), len(fld))
Author by
JJ.
Updated on June 14, 2022Comments
-
JJ. almost 2 years
Table
Email
:Values:
[email protected] [email protected] [email protected]
I want to replace the string before
@
withtest
.Result:
[email protected] [email protected] [email protected]
How do I use substringing and replace based on a character in the string?
-
t-clausen.dk over 11 years@Testifier did you try all the solutions ?
-
Tim Schmelter over 11 yearsREVERSE is one of the less performant string functions in TSQL.
-
Travis Peterson over 7 yearsI used this with LEFT to remove any trailing spaces at the end of a string. It worked perfectly. Even strings like "this " returned "this". Thanks for your solution.