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
.
You can use REPLACE in an UPDATE statement.
SQL Server REPLACE() Function The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive.
There are two ways to update a string in the database: Replace the string in the database with the current value of the StringObject object. Replace the old string in the database with the contents of an external file.
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')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With