T-SQL string replace in Update
107,965
Solution 1
The syntax for REPLACE:
REPLACE (string_expression,string_pattern,string_replacement)
So that the SQL you need should be:
UPDATE [DataTable] SET [ColumnValue] = REPLACE([ColumnValue], 'domain2', 'domain1')
Solution 2
If anyone cares, for NTEXT
, use the following format:
SELECT CAST(REPLACE(CAST([ColumnValue] AS NVARCHAR(MAX)),'find','replace') AS NTEXT)
FROM [DataTable]
Solution 3
update YourTable
set YourColumn = replace(YourColumn, '@domain2', '@domain1')
where charindex('@domain2', YourColumn) <> 0
Author by
Sekhar
Updated on November 05, 2020Comments
-
Sekhar over 3 years
I need to update the values of a column, with a substring replace being done on the existing values.
Example:
Data contains
abc@domain1
,pqr@domain2
etc.I need to update the values such that
@domain2
is replaced with@domain1
. -
deniz over 7 yearsDoes including the where clause as in this answer provide any efficiency gains over the accepted answer?
-
Adam Hey about 7 yearsThat statement in itself doesn't execute the update. you need to do the following: UPDATE [DataTable] SET SET [ColumnValue] = CAST(REPLACE(CAST([ColumnValue] AS NVARCHAR(MAX)),'domain2','domain1') AS NTEXT) FROM [DataTable]
-
Daniel Butler over 5 yearsIn my case I needed to replace a double quote with a single quote. I used the following REPLACE([ColumnValue], '"', ''''). The end is four single quotes.