Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor for demonstrating various ways of applying the REPLACE string function.

-- T-SQL REPLACE function usage for string replacement

SELECT REPLACE('United States of America','States of America','Kingdom');

-- United Kingdom

-- T-SQL REPLACE function usage for character replacement

SELECT REPLACE('SQL Server 2008 Enterprise Edition','i','*');

-- SQL Server 2008 Enterpr*se Ed*t*on

-- Dynamic REPLACE function usage in table SELECT

SELECT TOP (7) ProductID,

ProductName=Name, ChangedName= REPLACE(Name,'bike','bicycle')

FROM AdventureWorks2008.Production.Product

WHERE Name like '%bike%'

ORDER BY NEWID()

/*

ProductID   ProductName             ChangedName

709         Mountain Bike Socks, M  Mountain bicycle Socks, M

876         Hitch Rack - 4-Bike     Hitch Rack - 4-bicycle

877         Bike Wash - Dissolver   bicycle Wash - Dissolver

879         All-Purpose Bike Stand  All-Purpose bicycle Stand

710         Mountain Bike Socks, L  Mountain bicycle Socks, L

*/

-- Nested REPLACE example - eliminating special characters from string

DECLARE @Text varchar(max) = 'qw$ert%yuiop!@ASDFGHJKL'

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

@Text,

'!',''),'@',''),'#',''),'$',''),'%',''),'^',''),'&',''),'*','')

-- qwertyuiopASDFGHJKL

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

-- User-defined function (UDF) for cleansing string

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

CREATE FUNCTION dbo.fnCleanString

               (@InputString NVARCHAR(MAX),

                @KeepNchars   NVARCHAR(512))

RETURNS NVARCHAR(MAX)

  BEGIN

    DECLARE @Pattern NVARCHAR(1024) = '%[^' + @KeepNchars + ']%'

    WHILE PATINDEX(@Pattern,@InputString) >= 1

      SET @InputString = REPLACE(@InputString,Substring(@InputString,

                         PATINDEX(@Pattern,@InputString), 1),'')

    RETURN @InputString

  END

GO

SELECT CleanString=dbo.fnCleanString (N'James Bond 007', N'abcdefghijklmnopqrstuvwxyz')

-- JamesBond

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