REPLACE Function

Let's say that you have a customer database and you need to update all of your customers' addresses.  You need to change every instance of "Street" to "St."  How would you go about easily doing this using a T-SQL query?

Microsoft SQL Server has a nifty function called REPLACE().  Following is the basic syntax for the REPLACE function:

REPLACE ( string_expression , string_pattern , string_replacement )

In our example above, we would put the field of our address in the "string_expression", the value "Street" in the "string_pattern" and "St." in the "string_replacement:.  You would have a query much like this:

update customer
set address1 = REPLACE(address1, 'Street', 'St.')
where address1 like '%Street%'

You can also use REPLACE() to reference a value in one field to update another field.  For example, instead of using strings in "string_pattern" and "string_replacement", you can use fields in your table.  If the customer table contains both a phone number and an extension for your customer, you could use this to update the customer's call records.  A query would look something like this:

update RatedCall
set CallingNumber = REPLACE(CallingNumber, ANI, Customer.Extension)
from RatedCall, Customer
where RatedCall.CustomerNumber = Customer.CustomerNumber

Where ANI is the customer's phone number and extension is their extension (CallingNumber is the ANI is the call record).  This would only update instances where CallingNumber is the customer's phone number.

In what cases at your organization could you see using the REPLACE() function?

To read up more on SQL REPLACE(), go here: SQL REPLACE()