Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL string replace in Update

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.

like image 370
Sekhar Avatar asked Dec 03 '10 22:12

Sekhar


People also ask

Can we use Replace with update in SQL?

You can use REPLACE in an UPDATE statement.

How do I replace a string with another string in SQL?

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.

How do you update a string?

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.


1 Answers

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') 
like image 71
Kofi Sarfo Avatar answered Oct 13 '22 16:10

Kofi Sarfo