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.

Oracle:

  -- 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

Summary information:

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

• Oracle to SQL Server Migration Knowledge Base

Oracle 11g R2 SQL Language Reference

• TRANSLATE Function

Microsoft SQL Server 2012 - Books Online

• REPLACE Function

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