Translate function for SQL Server

CREATE FUNCTION dbo.Translate8K
(
  @String   varchar(8000), 
  @FromChar varchar(100), 
  @ToChar   varchar(100)
)
/****************************************************************************************
Purpose:
 Takes an input string (@string) and returns a new string with all occurrences of each 
 character in @FromChar replaced by its corresponding character in @ToChar. Take the
 input string "1122", a @FromChar value of "12" and a @ToChar value of "AB"; the result
 of dbo.Translate8K('1122', '12', 'AB') would return "AABB". Each "1" is replaced by "A",
 each "2" is replaced by "B". 
 
 Characters in @string that don't exist in @FromChar remain unchanged. Changing the above
 example where @string is "112233", @FromChar is "AB" and @ToChar is "12"; the result of
 dbo.Translate8K('112233', '12', 'AB') returns "AABB33". When @FromChar contains more 
 characters than @ToChar, the extra characters at the end of @FromChar that have no 
 corresponding characters in @ToChar are simply removed from @String and don't appear in
 the return value.

Compatibility: 
 SQL Server 2008+, Azure SQL Database, Azure SQL Data Warehouse & Parallel Data Warehouse
 
Parameters:
 @String   = varchar(8000); The input string to "translate"
 @FromChar = varchar(100);  Containing a series of characters to search for in @String
 @ToChar   = varchar(100);  All characters in the @FromChar will be replaced with the 
             corresponding character in the @ToChar.

Returns: varchar(8000); the "tranlated string"

Developer notes:
 1. Scalar user defined functions (udf) generally perform badly and "Inline" scalar UDFs
    generally perform much better. The only way to get this logic into an "Inline scalar 
    udf" would be to use a recursive CTE which, for this task, performs very badly. For
    more about "in scalar UDFs" see:
    http://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx

 2. When @ToChar is longer than @FromChar the replacement characters that
    the characters in @ToChar past without a corresponding character in 
    @FromChar are ignored. 

 3. Note that, becauase Translate8K is a scalar udf it will force the optimizer to choose 
    a serial plan even when a parallel plan would perform better. If you can get away
    with using the built-in T-SQL REPLACE function then use that instead. 

 4. Translate8K is case sensitive; to change this remove: "COLLATE Latin1_General_BIN"

 5. Translate8K is deterministic. For more about deterministic functions see:
    https://msdn.microsoft.com/en-us/library/ms178091.aspx

Usage Examples:
--===== (1) basic replace characters/remove characters; 
 -- replace a with A, c with C, b with x and remove $ and #
 DECLARE @string varchar(20)='###$$$aaabbbccc$$$###';
 SELECToriginal = @string, translated = dbo.Translate8K(@string,'acb#$','ACx');

--===== (2) Apply a specific format to a phone number
 DECLARE @string varchar(8000) = '(425) 555-1212';
 SELECT original = @string, Translated = dbo.Translate8K(@string,')(','-');

--===== (3) hide phone numbers, retain existing format
 WITH phoneNbrs(n,pn) AS 
 (
    SELECT 1, '(425) 555-1212' UNION ALL SELECT 2, '425.555.1212' UNION ALL
    SELECT 3, '425-555-1212'   UNION ALL SELECT 4, '4255551212' 
 )
 SELECT n, pn AS before, [after] = dbo.Translate8K(pn,x,y)
 FROM phoneNbrs
 CROSS APPLY (VALUES('()-.0123456789','()-.**********')) t(x,y);

--===== (4) Replace accent characters with normal characters (note the "double translate")
 DECLARE 
   @string   varchar(100) = 'Thë Quìck Greeñ Fox jumpëd over thë lázy dogs back!',
   @special1 varchar(32)  = 'áâãäæèïéìëíîçåñòóôöõàøúüûùýÁÃÄ',
   @normal1  varchar(32)  = 'aaaaaeieieiicanoooooaouuuuyAAAAA',
   @special2 varchar(32)  = 'ÆÈÏÉÌËÍÎÅÑÒÓÔÖÕÀØÚÜÛÙÝ!',
   @normal2  varchar(32)  = 'EIEIEIIANOOOOOAOUUUUY.';

 SELECT
   original  = @string, 
   newstring = 
     dbo.Translate8K(dbo.Translate8K(@string,@special1,@normal1),@special2,@normal2);

------------------------------------------------------------------------------
Revision History:
 Rev 00 - 20150518 - Initial Development - Alan Burstein
 Rev 01 - 20160401 - Reduced the number of rows in the tally table to only 100 numbers 
                     (the length of @ToChar). - Alan Burstein 
****************************************************************************************/RETURNS varchar(8000) WITH SCHEMABINDING AS 
BEGIN
 SELECT @string = REPLACE
  (
    @string COLLATE Latin1_General_BIN,
    SUBSTRING(@FromChar,N,1),
    SUBSTRING(@ToChar,N,1)
  )
 FROM
 (
  SELECT TOP(DATALENGTH(@FromChar)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT 1)))
  FROM (VALUES
        (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
        (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
        (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
        (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
        (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
        (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
        (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
        (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
        (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
        (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) t(N)
 ) iTally(N) -- 100 row tally table
 RETURN @string;
END;
GO