The function for string substitution in Microsoft SQL Server is called REPLACE.

Use it by invoking REPLACE(@yourString, @searchFor, @replaceWith);.

For example:

SELECT REPLACE('This is a haystack', 'haystack', 'needle');

This would produce:

This is a needle
(1 rows affected)

Since it’s a function you can, for example, also use it in where clauses or in update statements.

Example:

UPDATE 
   YourTable
SET
   YourColumn = REPLACE(YourColumn, 'foo', 'bar');