SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE numbers (
  id INT,
  PRIMARY KEY CLUSTERED (id)
);

WITH tens(id) AS (SELECT * FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) x(id))
INSERT INTO numbers (id)
SELECT b4.id*1000 + b3.id*100 + b2.id*10 + b1.id FROM tens b4, tens b3, tens b2, tens b1;
10000 rows affected

CREATE FUNCTION dbo.tvfTranslate(
  @string        NVARCHAR(MAX),
  @characters    NVARCHAR(MAX),
  @translations  NVARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN (
  SELECT
    newString =
      (
        SELECT
          CASE WHEN replace_from.position > 0 THEN replace_with.string ELSE string.current_char END
        FROM
          numbers
          CROSS APPLY (SELECT SUBSTRING(@string, numbers.id, 1)                 ) string(current_char)
          CROSS APPLY (SELECT CHARINDEX(string.current_char, @characters)       ) replace_from(position)
          CROSS APPLY (SELECT SUBSTRING(@translations, replace_from.position, 1)) replace_with(string)
        WHERE
              numbers.id >  0
          AND numbers.id <= LEN('_' + @characters + '_') - 2
        ORDER BY
          numbers.id
        FOR XML PATH(''), TYPE
      ).value('(text())[1]', 'varchar(8000)')
)
SELECT
  *
FROM
(
  VALUES
    (1, N'abc', N'abc', N'bcd')
   ,(2, N'abc', N'abc', N'bc' )
   ,(3, N'abc', N'aba', N'bab')
   ,(4, N'abc', N'aba', N'bad')
   ,(5, N'abc', N'ab',  N'bad')
)
  test(id, string, characters, translations)
CROSS APPLY
  dbo.tvfTranslate(string, characters, translations) tvf


id
string
characters
translations
newString


1
abc
abc
bcd
bcd

2
abc
abc
bc
bc

3
abc
aba
bab
bac

4
abc
aba
bad
bac

5
abc
ab
bad
ba


CREATE FUNCTION dbo.Translate(
  @string        NVARCHAR(MAX),
  @characters    NVARCHAR(MAX),
  @translations  NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

  WITH
    dictionary AS
  (
    SELECT
      id, string_from, string_to
    FROM
    (
      SELECT
        ROW_NUMBER() OVER (    ORDER BY string_from            )  AS id,
        ROW_NUMBER() OVER (PARTITION BY string_from ORDER BY id)  AS occurence,
        string_from,
        string_to
      FROM
        numbers
      CROSS APPLY
      (
        SELECT
          CAST(SUBSTRING(@characters,   numbers.id, 1) AS NVARCHAR(5))  AS string_from,
          CAST(SUBSTRING(@translations, numbers.id, 1) AS NVARCHAR(5))  AS string_to
      )
        chars
      WHERE
            numbers.id >  0
        AND numbers.id <= LEN('_' + @characters + '_') - 2
    )
      sorted_dictionary
    WHERE
      occurence = 1
  ),
    pivot_look_forward AS
  (
    SELECT
      dict.*,
      p.context,
      string_to_as_string_from_id = MAX(CASE WHEN context = 'f' THEN p.id END) OVER (PARTITION BY string)
    FROM
      dictionary   dict
    CROSS APPLY
    (
      VALUES (id, string_from, 'f'),
             (id, string_to,   't')
    )
      p(id, string, context)
  ),
    mapping_sequence AS
  (
    SELECT
      map.*
    FROM
      pivot_look_forward  dict
    CROSS APPLY
    (
      SELECT CASE WHEN dict.string_to_as_string_from_id > dict.id THEN 1 END
    )
      future_key(id)
    CROSS APPLY
    (
      SELECT 2, dict.id,        dict.string_from,               dict.string_to          WHERE future_key.id IS NULL     AND dict.string_from NOT IN (N'<', N'>')
      UNION ALL
      SELECT 2, dict.id,        dict.string_from,        N'<' + dict.string_from + N'>' WHERE future_key.id IS NOT NULL AND dict.string_from NOT IN (N'<', N'>')
      UNION ALL
      SELECT 3, dict.id, N'<' + dict.string_from + N'>',        dict.string_to          WHERE future_key.id IS NOT NULL AND dict.string_from NOT IN (N'<', N'>')
      UNION ALL
      SELECT 0, dict.id,        dict.string_from,        N'<' + dict.string_from + N'>' WHERE                               dict.string_from     IN (N'<', N'>')
      UNION ALL
      SELECT 4, dict.id, N'<' + dict.string_from + N'>',        dict.string_to          WHERE                               dict.string_from     IN (N'<', N'>')
    )
      map(phase_id, id, string_from, string_to)
    WHERE
      dict.context = 't'
    
    UNION ALL
    
    SELECT 1,        0,  N'<<<>>',    N'<<>'    WHERE @from_chars LIKE N'%<%' AND @from_chars LIKE N'%>%'
  )
  SELECT
    @string = REPLACE(@string, string_from, string_to)
  FROM
    mapping_sequence
  ORDER BY
    phase_id, id
  ;

  RETURN @string;

END
Msg 137 Level 15 State 2 Line 83
Must declare the scalar variable "@from_chars".

WITH
  tests(id, replace_string, from_chars, to_chars) AS
(
              SELECT 1, N'abc<defghijklm<nop qr stuvw<>xyz<>z><>asd',     N'a<>b cz', N'b><c-da'
    UNION ALL SELECT 2, N'abc<defghijklm<nop qr stuvw<>x><>xyz<><z><asd', N'a><b cz', N'b<>c-da'
    UNION ALL SELECT 3, N'abc<defghijklm<nop qr stuvw<>x<><xyz><>z><asd', N'a<>b cz', N'b><c-da'
    UNION ALL SELECT 4, N'abc\defghijk lmnop qrstuvw\x\xyz\\<>',          N'abc ',    N'bcd-'
    UNION ALL SELECT 5, N'abc',                                           N'aba' ,    N'bab'
)
SELECT *, dbo.Translate(replace_string, from_chars, to_chars),TRANSLATE(replace_string, from_chars, to_chars) FROM tests
EXCEPT
SELECT *, TRANSLATE(replace_string, from_chars, to_chars), TRANSLATE(replace_string, from_chars, to_chars) FROM tests
;
Msg 4121 Level 16 State 1 Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Translate", or the name is ambiguous.

WITH
  tests(id, replace_string, from_chars, to_chars) AS
(
    SELECT 1, N'abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd', N'xyz', N'ab'
)
SELECT *, TRANSLATE(replace_string, from_chars, to_chars) FROM tests
;
Msg 9828 Level 16 State 3 Line 1
The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

WITH
  tests(id, replace_string, from_chars, to_chars) AS
(
    SELECT 1, N'abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd', N'xyz', N'ab'
)
SELECT *, dbo.translate(replace_string, from_chars, to_chars) FROM tests
;
Msg 4121 Level 16 State 1 Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.translate", or the name is ambiguous.

WITH
  tests(id, replace_string, from_chars, to_chars) AS
(
    SELECT 1, N'abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd', N'abc', N'abcd'
)
SELECT *, TRANSLATE(replace_string, from_chars, to_chars) FROM tests
;
Msg 9828 Level 16 State 3 Line 1
The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

WITH
  tests(id, replace_string, from_chars, to_chars) AS
(
    SELECT 1, N'abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd', N'abc', N'abcd'
)
SELECT *, dbo.translate(replace_string, from_chars, to_chars) FROM tests
;
Msg 4121 Level 16 State 1 Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.translate", or the name is ambiguous.
id	string	characters	translations	newString
1	abc	abc	bcd	bcd
2	abc	abc	bc	bc
3	abc	aba	bab	bac
4	abc	aba	bad	bac
5	abc	ab	bad	ba
id	string	characters	translations	newString
1	abc	abc	bcd	bcd
2	abc	abc	bc	bc
3	abc	aba	bab	bac
4	abc	aba	bad	bac
5	abc	ab	bad	ba