Share via

Implementation of Translate() Function in MS SQL Server

  • Article

Translate Function:

In Oracle/PLSQL, the translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

The syntax for the translate function is:

translate( string1, string_to_replace, replacement_string )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string - All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.

Eaxmples :

translate('1tech23', '123', '456'); would return '4tech56'
translate('222tech', '2ec', '3it'); would return '333tith'

I have implemented the same for MS SQL Server. The syntax would be similar as of translate in Oracle.

Syntax : Translate(Data,DataToReplace,ReplacedWithData)

Data : Is the string can be of VARCHAR(MAX)
DataToReplace : Is the characters can be of VARCHAR(100)
ReplacedWithData :  Is the characters can be of VARCHAR(100)


  01.      USE AdventureWorks  
  02.      GO  
  03.   
  04.      IF OBJECT_ID (N    'dbo.Translate'    , N    'FN'    )     IS NOT NULL
  05.                    DROP FUNCTION dbo.Translate;  
  06.      GO  
  07.      CREATE FUNCTION dbo.Translate (@Data VARCHAR(MAX), @DataToReplace VARCHAR(100), @ReplacedWithData VARCHAR(100))
  08.      RETURNS VARCHAR(MAX)
  09.   
  10.      BEGIN  
  11.                 
  12.                    DECLARE @TranslaedData VARCHAR(MAX)  
  13.                 
  14.                    ;      WITH CTE(PosToReplace,Data,DataToReplace,ReplacedWithData) AS  
  15.                    (      
  16.                    SELECT 1,CAST(@Data AS VARCHAR(MAX)) AS Data,CAST(SUBSTRING(@DataToReplace,1,1) AS VARCHAR(MAX)) AS DataToReplace,CAST(SUBSTRING(@ReplacedWithData,1,1) AS VARCHAR(MAX)) AS ReplacedWithData  
  17.                    UNION ALL  
  18.                    SELECT C.PosToReplace+1 AS PosToReplace , CAST(REPLACE(C.Data,C.DataToReplace,C.ReplacedWithData) AS VARCHAR(MAX)) AS Data,CAST(SUBSTRING(@DataToReplace,PosToReplace+1,1) AS VARCHAR(MAX)) AS DataToReplace,CAST(SUBSTRING(@ReplacedWithData,PosToReplace+1,1) AS VARCHAR(MAX)) AS ReplacedWithData  
  19.                    FROM CTE C  
  20.                    WHERE C.PosToReplace <= LEN(@DataToReplace)  
  21.                    )      
  22.                    SELECT  @TranslaedData = C.Data FROM CTE C WHERE C.PosToReplace = LEN(@DataToReplace)+1  
  23.                     
  24.                    RETURN @TranslaedData                             
  25.                 
  26.      END

 

Perhaps these can be modified easily.

I am also including the code snippet so other users can also benifit from it.

Note : This is my first contribution at Gallery. Please let mw know about this if you have any suggestions i am very much open to hear it from you guys.

Thanks,
Hasham