Microsoft® SQL Server® 2012 Bible

200

Part II: Building Databases and Working with Data

REPLACE (string, string)

The Replace() function operates as a global search and replace within a string. Using

REPLACE() within an update DML command can quickly fi x problems in the data, such as

removing extra tabs or correcting string patterns. The following code sample adds apostro-

phes to the Name column in the AdventureWorks database’s Product table:

-- Create test case by modifying one product's name.

USE AdventureWorks

GO

UPDATE Production.Product

SET

Name = 'Chain Stay''s'

WHERE

Name = 'Chain Stays'

SELECT

Name,

REPLACE(Name, '''', '') Replaced

FROM Production.Product

WHERE

ProductID = 324

Result:

Name Replaced

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

Chain Stay's Adams

To demonstrate the REPLACE() function using an update command, the next query actu-

ally changes the data in place and removes any apostrophes:

USE AdventureWorks

GO

UPDATE Production.Product

SET

Name = REPLACE(Name, '''', '')

WHERE

ProductID = 324

SELECT ProductID,

Name

FROM Production.Product

WHERE

ProductID = 324

Result:

c08.indd 200c08.indd 200 7/30/2012 4:21:16 PM7/30/2012 4:21:16 PM

http://www.it-ebooks.info