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)

 

SQL
Edit|Remove
USE AdventureWorks 
GO 
 
IF OBJECT_ID (N'dbo.Translate'N'FN'IS NOT NULL 
    DROP FUNCTION dbo.TranslateGO 
CREATE FUNCTION dbo.Translate (@Data VARCHAR(MAX), @DataToReplace VARCHAR(100), @ReplacedWithData VARCHAR(100)) 
RETURNS VARCHAR(MAX) 
 
BEGIN 
     
    DECLARE @TranslaedData VARCHAR(MAX) 
     
    ;WITH CTE(PosToReplace,Data,DataToReplace,ReplacedWithDataAS  
    ( 
    SELECT 1,CAST(@Data AS VARCHAR(MAX)) AS Data,CAST(SUBSTRING(@DataToReplace,1,1AS VARCHAR(MAX)) AS DataToReplace,CAST(SUBSTRING(@ReplacedWithData,1,1AS VARCHAR(MAX)) AS ReplacedWithData 
    UNION ALL 
    SELECT C.PosToReplace+1 AS PosToReplace , CAST(REPLACE(C.Data,C.DataToReplace,C.ReplacedWithDataAS VARCHAR(MAX)) AS Data,CAST(SUBSTRING(@DataToReplace,PosToReplace+1,1AS VARCHAR(MAX)) AS DataToReplace,CAST(SUBSTRING(@ReplacedWithData,PosToReplace+1,1AS VARCHAR(MAX)) AS ReplacedWithData 
    FROM CTE C 
    WHERE C.PosToReplace <= LEN(@DataToReplace) 
    ) 
    SELECT  @TranslaedData = C.Data FROM CTE C WHERE C.PosToReplace = LEN(@DataToReplace)+1 
         
    RETURN @TranslaedData                             
     
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 me know about this if you have any suggestions i am very much open to hear it from you guys.

Thanks,
Hasham