Microsoft® SQL Server® 2012 Bible

304

Part II: Building Databases and Working with Data

Result:

AddressID Address1

---------- -------------

1 1970 Napa Court

Performing Global Search and Replace

Cleaning up bad data is a common database developer task. Fortunately, SQL includes a

REPLACE() function, which when combined with the UPDATE command can serve as a

global search and replace. You can use this to remove extra tabs from data.

In the following example, which references the AdventureWorks sample database, every

occurrence of 'Sun' in the County column is updated to 'Dark':

Use AdventureWorks

Go

UPDATE Address

SET County = REPLACE(County, 'Sun', 'Dark')

WHERE County LIKE '%Shine';

The following SELECT statement examines the result of the REPLACE() function:

Select County from Address

WHERE County LIKE '%Shine';

Result (abbreviated):

County

---------

Darkshine

Darkshine

Darkshine

Darkshine

...

Referencing Multiple Tables While Updating Data

A more powerful function of the SQL UPDATE command is setting a column to an expres-

sion that can refer to the same column, other columns, or even other tables.

Although expressions are certainly available within a single-table update, expressions often

need to reference data outside the updated table. The optional FROM clause enables joins

between the table being updated and other data sources. Only one table can be updated,

but when the table is joined to the corresponding rows from the joined tables, the data

from the other columns is available within the UPDATE expressions.

c12.indd 304c12.indd 304 7/30/2012 4:42:40 PM7/30/2012 4:42:40 PM

http://www.it-ebooks.info