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:
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:
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()