TRANSLATE Function - IBM DB2 to SQL Server Migration

In DB2 you can use the TRANSLATE function to replace the specified characters in string with other characters. SQL Server also provides TRANSLATE function but it has the different order of parameters.

TRANSLATE is available since SQL Server 2017 only so for earlier versions you can use nested REPLACE expressions to replace each character individually (see an example below).

Also in SQL Server the number of replacement characters must match the number of replaced characters. .

DB2:

  -- Replace 'a' with '1', 'd' with '2' and 'e' with '3'
  SELECT TRANSLATE('abcdef', '123', 'ade') FROM sysibm.sysdummy1;
 # 1bc23f

SQL Server:

  -- Replace 'a' with '1', 'd' with '2' and 'e' with '3' (note the different order of parameters)
  SELECT TRANSLATE('abcdef', 'ade', '123') 
  # 1bc23f

In DB2 if the replacement string is shorter than the replaced characters string then other characters are replaced with blank character.

DB2:

  -- Replace 'a' with '1', 'd' with '2' and 'e' with ' '
  SELECT TRANSLATE('abcdef', '12', 'ade') FROM sysibm.sysdummy1;
 # 1bc2 f

In SQL Server the lengths of the strings must match so you have to specify blanks as the replacement characters explicitly:

  -- Try to use shorter replacement string
  SELECT TRANSLATE('abcdef', 'ade', '12') 
  # Msg 9828, Level 16, State 1, Line 1
  # The second and third arguments of the TRANSLATE built-in function must contain 
  # an equal number of characters.
 
  -- Replace 'a' with '1', 'd' with '2' and 'e' with ' ' (adding ' ' as replacement character explicitly)
  SELECT TRANSLATE('abcdef', 'ade', '12 ');
 # 1bc2 f

TRANSLATE function is available since SQL Server 2017 version, so for earlier versions you can use REPLACE function to replace every character as follows:

SQL Server:

  -- Replace 'a' with '1', 'd' with '2' and 'e' with '3'
  SELECT REPLACE(REPLACE(REPLACE('abcdef', 'a', '1'), 'd', '2'), 'e', '3')
  # 1bc23f

For more information, see IBM DB2 to SQL Server Migration.