Jeff Moden (2/5/2010)

---

Ramesh Saive (2/5/2010)

---

I don't think so there is any equivalent function in SQL Server. But you can create you own function using the existing REPLACE function.

Heh.. Let's see some code. I believe you'll find it's a wee bit more difficult to do than just using REPLACE.

DECLARE @Characters TABLE( SomeChar CHAR(1) NOT NULL )
DECLARE @SomeText VARCHAR(100)

SELECT	@SomeText = 'Ticking away the moments that make up a dull day, Fritter and waste the hours in an offhand way.'

INSERT	@Characters( SomeChar )
SELECT	't' UNION ALL
SELECT	'i' UNION ALL
SELECT	'c'

SELECT	@SomeText = REPLACE( @SomeText, SomeChar, '' )
FROM	@Characters

PRINT @SomeText

--==============================================================================
--      Setup the test tables
--==============================================================================
--===== Conditionally remove the test tables so we can easily retest
     IF OBJECT_ID('TempDB..#Dirty','U') IS NOT NULL
        DROP TABLE #Dirty;

     IF OBJECT_ID('TempDB..#Clean','U') IS NOT NULL
        DROP TABLE #Clean;
GO
--===== Build the "dirty" table where each row has
     -- at least 4 characters to remove
 SELECT TOP 100000
        CAST(NEWID() AS VARCHAR(100)) AS SomeText
   INTO #Dirty
   FROM Master.sys.All_Columns ac1,
        Master.sys.All_Columns ac2;

--===== Build the "clean" table where each row has
     -- NO characters to remove
 SELECT TOP 100000
        REPLICATE('B',26) AS SomeText
   INTO #Clean
   FROM Master.sys.All_Columns ac1,
        Master.sys.All_Columns ac2;
GO

--==============================================================================
--      Setup the functions to test
--==============================================================================
--===== Do these tests in a nice safe place that everyone has.
    USE TempDB;
GO
--===== Build a function using "Replace"
 CREATE FUNCTION dbo.CleanWithReplace
        (@SomeText VARCHAR(100))
RETURNS VARCHAR(100)
     AS
  BEGIN
        DECLARE @Characters TABLE(SomeChar CHAR(1) NOT NULL)
         INSERT  @Characters( SomeChar )
         SELECT  'A' UNION ALL
         SELECT  'E' UNION ALL
         SELECT  '-'

         SELECT @SomeText = REPLACE( @SomeText, SomeChar, '' )
           FROM @Characters

 RETURN @SomeText
    END;
GO
--===== Build a function using "Stuff"
 CREATE FUNCTION dbo.RemoveNonPrintable
     -- Modified by Jeff Moden
        (@String VARCHAR(8000))
RETURNS VARCHAR(8000) AS
  BEGIN
DECLARE @IncorrectCharLoc SMALLINT,
        @Pattern          CHAR(7)

 SELECT @Pattern = '%[-AE]%',
        @IncorrectCharLoc = PATINDEX(@Pattern, @String)

  WHILE @IncorrectCharLoc > 0
 SELECT @string = STUFF(@String, @IncorrectCharLoc, 1, ''),
        @IncorrectCharLoc = PATINDEX(@Pattern, @String)
 RETURN @string
    END
GO

--==============================================================================
--      Now we'll do the tests.  I have Profiler measuring SQL:Batch Completed
--      against the SPID that I'm running these tests from.  Also notice that
--      I've taken the time to take the display the results out of the picture 
--      by returning the result to a "throw away" variable (@BitBucket).
--==============================================================================
GO
--===== CleanWithStuff against "dirty"
DECLARE @BitBucket VARCHAR(8000);
 SELECT @BitBucket = dbo.CleanWithStuff(SomeText)
   FROM #Dirty;
GO
--===== CleanWithReplace against "dirty"
DECLARE @BitBucket VARCHAR(8000);
 SELECT @BitBucket = dbo.CleanWithReplace(SomeText)
   FROM #Dirty;
GO
--===== CleanWithStuff against "clean"
DECLARE @BitBucket VARCHAR(8000);
 SELECT @BitBucket = dbo.CleanWithStuff(SomeText)
   FROM #Clean;
GO
--===== CleanWithReplace against "clean"
DECLARE @BitBucket VARCHAR(8000);
 SELECT @BitBucket = dbo.CleanWithReplace(SomeText)
   FROM #Clean;
GO

 CREATE FUNCTION dbo.RemoveNonDisplayChars
/********************************************************************
 Purpose:
 Remove the non-displayable control characters from CHAR(0) to 
 CHAR(31) and the DELETE character CHAR(127).

 Revision History:
 Rev 00 - Jeff Moden - 06 Feb 2010 - Initial Release and Unit Test
********************************************************************/
--===== Declare the I/O parameters
        (@pString VARCHAR(8000))
RETURNS VARCHAR(8000) 
     AS
  BEGIN

--===== Declare Local variables
DECLARE @IncorrectCharLoc SMALLINT, --Position of bad character
        @Pattern          CHAR(37)  --Bad characters to look for

 SELECT @Pattern          = '%['
                          + CHAR(0)+CHAR(1)+CHAR(2)+CHAR(3)+CHAR(4)
                          + CHAR(5)+CHAR(6)+CHAR(7)+CHAR(8)+CHAR(9)
                          + CHAR(10)+CHAR(11)+CHAR(12)+CHAR(13)+CHAR(14)
                          + CHAR(15)+CHAR(16)+CHAR(17)+CHAR(18)+CHAR(19)
                          + CHAR(20)+CHAR(21)+CHAR(22)+CHAR(23)+CHAR(24)
                          + CHAR(25)+CHAR(26)+CHAR(27)+CHAR(28)+CHAR(29)
                          + CHAR(30)+CHAR(31)+CHAR(127)
                          + ']%',
        @IncorrectCharLoc = PATINDEX(@Pattern, @pString)

  WHILE @IncorrectCharLoc > 0
 SELECT @pString          = STUFF(@pString, @IncorrectCharLoc, 1, ''),
        @IncorrectCharLoc = PATINDEX(@Pattern, @pString)
 RETURN @pString
    END
GO

CREATE  FUNCTION dbo.IF_CleanWithReplace
        (
        @SomeText VARCHAR(100)
        )
RETURNS TABLE
WITH    SCHEMABINDING
AS      RETURN
        SELECT  cleaned = 
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                @SomeText COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
                                'A', SPACE(0)),
                            'E', SPACE(0)),
                        '-', SPACE(0));

DECLARE @BitBucket VARCHAR(8000);
SELECT  @BitBucket = iTVF.cleaned
FROM    #Dirty D
CROSS
APPLY   dbo.IF_CleanWithReplace(D.SomeText) iTVF;