Implementation of Translate Function in MS SQL Server

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)

 

USE AdventureWorks
GO
 
IF OBJECT_ID (N'dbo.Translate', N'FN') IS  NOT NULL
    DROP FUNCTION  dbo.Translate;
GO
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,ReplacedWithData) AS
    (
    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
    UNION ALL
    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
    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 mw know about this if you have any suggestions i am very much open to hear it from you guys.

Thanks, Hasham

Above code is not looking for lowercase or uppercase. Below code will work for all.

Thanks,
Krushna

IF OBJECT_ID (N'dbo.fn_translate', N'FN') IS NOT NULL     DROP FUNCTION dbo.fn_translate; GO -- ============================================================      -- Author  : Krushna Kadam -- Create date : 4-Apr-2012 -- Description : Function to return the translated value of provided diacritics. -- Spec ID  : Spec #657  -- ============================================================      CREATE FUNCTION dbo.fn_translate ( @string_in NVARCHAR(4000)) RETURNS NVARCHAR(4000) AS BEGIN DECLARE @string_1 NVARCHAR(4000) DECLARE @string_2 NVARCHAR(4000) IF (@string_1 IS NULL OR @string_2 IS NULL) BEGIN  SET @string_1='ªµºÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜYßàáâãäåæçèéêëìíîï¿ñòóôõöùúûüÿ¿øØÞðп¿¿¿Ýý¿ÿ'  SET @string_2='auoAAAAAAACEEEEIIIINOOOOOUUUUYSaaaaaaaceeeeiiiiDnooooouuuuyyoODdDOoSsYyYy' END DECLARE @string_out NVARCHAR(4000) DECLARE @string  CHAR(1) DECLARE @i   SMALLINT DECLARE @pos  SMALLINT DECLARE @list TABLE ( indx SMALLINT,       string CHAR(1),       trans BIT) IF LEN(@string_1) != LEN(@string_2) BEGIN  SELECT @string_out = NULL  RETURN @string_out END SELECT @string_out = '', @i = 1, @pos = 0 WHILE @i <= LEN(@string_in) BEGIN  INSERT INTO @list VALUES (@i,SUBSTRING(@string_in, @i, 1),0)  SELECT @i = @i + 1 END SELECT @i = 1 WHILE @i <= LEN(@string_1) BEGIN  UPDATE @list  SET string = SUBSTRING(@string_2, @i, 1),   trans = 1  WHERE ASCII(string) = ASCII(SUBSTRING(@string_1, @i, 1))  AND trans = 0  SELECT @i = @i + 1 END SELECT @pos = MAX(indx), @i = 1 FROM @list WHILE @i <= @pos BEGIN  SELECT @string = string  FROM @list  WHERE indx = @i  SELECT @string_out = @string_out + @string  SELECT @i = @i + 1 END RETURN @string_out END GO