REPLACE( original-string, search-string, replace-string )

If any argument is NULL, the function returns NULL.

• LONG VARCHAR
• LONG NVARCHAR

This function replaces all occurrences.

Comparisons are case-sensitive on case-sensitive databases.

This function supports NCHAR inputs and/or outputs.

• SUBSTRING function [String]
• CHARINDEX function [String]
• String functions

The following statement returns the value xx.def.xx.ghi.

SELECT REPLACE( 'abc.def.abc.ghi', 'abc', 'xx' );

The following statement generates a result set containing ALTER PROCEDURE statements which, when executed, would repair stored procedures that reference a table that has been renamed. (To be useful, the table name must be unique.)

SELECT REPLACE(
   REPLACE( proc_defn, 'OldTableName', 'NewTableName' ),
   'CREATE PROCEDURE',
   'ALTER PROCEDURE')
FROM SYS.SYSPROCEDURE
WHERE proc_defn LIKE '%OldTableName%';