DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to REPLACE substring with another string?

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%[email protected]'

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

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

 

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE