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