TRANSLATE - Character Substitution - Oracle to SQL Server Migration

In Oracle, TRANSLATE function allows you to perform one-to-one, single character substitution in a string. In SQL Server, you can use REPLACE function to replace each character or an user-defined function.


  -- Replace # and blank with _
  SELECT TRANSLATE('Unit Number#2', '# ', '__') FROM dual;
  # Unit_Number_2

SQL Server:

You can use a REPLACE for each character.

  -- Replace # and blank with _
  SELECT REPLACE(REPLACE('Unit Number#2', '#', '_'), ' ', '_');
  # Unit_Number_2

TRANSLATE Conversion Overview

Summary information:

Oracle SQL Server
Syntax TRANSLATE(string, from_multiple, to_multiple) Nested REPLACE(string, from_single, to_single)
Alternatives Nested REPLACE function for each character

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012


Oracle 11g R2 SQL Language Reference

Microsoft SQL Server 2012 - Books Online

SQLines Services

SQLines offers database administration, optimization and migration services for Oracle and SQL Server databases and applications. For more information, please Contact Us.

You could leave a comment if you were logged in.