Let Us Learn Oracle - Part 8 of N [ Translate function in Oracle ]

Source table: tblPlayers

Syntax: Translate(char, from,to)

Purpose: Replaces all characters of 'from' with characters of 'to' at that particular position

Example 1:

SQL> Select PlayerLastName,Translate(PlayerLastName,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ZYXWVUTSRQPONMLKJIHGFEDCBA') TranslateExample From tblPlayers;

PLAYERLAST TRANSLATEE
---------- ----------
Raman      Iaman
Kadir      Padir
Nadir      Madir
Das        Was
Punchkar   Kunchkar
Fateman    Uateman
Gajani     Tajani
Hariharan  Sariharan
Ikat       Rkat
Jwar       Qwar

10 rows selected.

We can infer two things from this small example

1. The letters in the 'from' part has been replaced by the letter in the 'to' part. Thus in the first row, 'R' in 'Raman' has been replaced by 'I'.
2. It is case sensitive. Thus the small letter has been unchanged. Making it all capital change the result as under

	SQL> Select PlayerLastName,Translate(UPPER(PlayerLastName),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ZYXWVUTSRQPONMLKJIHGFEDCBA') TranslateExample From tblPlayers;

	PLAYERLAST TRANSLATEE
	---------- ----------
	Raman      IZNZM
	Kadir      PZWRI
	Nadir      MZWRI
	Das        WZH
	Punchkar   KFMXSPZI
	Fateman    UZGVNZM
	Gajani     TZQZMR
	Hariharan  SZIRSZIZM
	Ikat       RPZG
	Jwar       QDZI

	10 rows selected.
	

It interpretes the empty string as null and ignores them

Example 2:

SQL> Select TRANSLATE('ABCD     EFGH','ABCDEFGH','abcdefgh') Example From Dual;

EXAMPLE
-------------
abcd     efgh

In Sql Server though we donot have such as function at present but we can have our own in place. Here is my attempt

-- =============================================
-- Author:		Niladri Biswas
-- Create date: 9/21/2011
-- Description:	 Translate
-- =============================================
CREATE FUNCTION [dbo].[Translate]
(
	-- Add the parameters for the function here
	@InputString varchar(4000)
	,@DataToReplace varchar(1000)
	,@ReplaceWith  varchar(1000)
)
RETURNS VARCHAR(4000)

AS
BEGIN
	-- Declare the return variable here
	Declare @OutputString   VARCHAR(4000)
	
	;With inputCTE As
	(
		Select 
			ID = Row_Number() Over(Order By (Select 1))
			,Data=@InputString
			,DataToReplace = @DataToReplace
			,ReplacedWithData = @ReplaceWith		
	)
	,letterForDataToReplaceCTE As
	(
		Select 	
			 ID
			 ,Rn = Row_Number() Over(Partition By ID Order By (Select 1))
			 ,CharToReplace = Substring(d.DataToReplace,n.Number,1)		
			From inputCTE As d
			Join master..spt_values As n on n.Type = 'P'
			Where n.Number Between 1 And Len(d.DataToReplace)
	),
	letterForReplacedDataCTE As
	(
		Select 
			 ID	
			, Rn = Row_Number() Over(Partition By ID Order By (Select 1))
			 ,ReplaceWith = Substring(d.ReplacedWithData,n.Number,1)		
			From inputCTE As d
			Join master..spt_values As n on n.Type = 'P'
			Where n.Number Between 1 And Len(d.ReplacedWithData)
	),
	letterForExpression As
	(
		Select 	
			ID
			,Rn = Row_Number() Over(Partition By ID Order By (Select 1))
			 ,Expression = Substring(d.Data,n.Number,1)		
			From inputCTE As d
			Join master..spt_values As n on n.Type = 'P'
			Where n.Number Between 1 And Len(d.Data)
	),
	charToReplaceCTE As
	(
		Select
				x.ID
				,RowNumCharToReplace = (Coalesce(x.Rn ,'-1'))
				,CharToReplace = (Coalesce(CharToReplace,''))
				,RowNumReplaceWith = (Coalesce(y.Rn ,'-1'))
				,ReplaceWith = (Coalesce(ReplaceWith,''))
		From letterForDataToReplaceCTE x
		Full Join letterForReplacedDataCTE y
		On x.Rn = y.Rn	
		And x.ID = y.ID		
	),
	matchLettersCTE As
	(
		Select 
			 e.ID
			 ,e.Rn
			,r.RowNumCharToReplace
			 ,Expression
			 ,CharToReplace
			 ,Status = Case When Expression = CharToReplace Then 1 Else 0 End
			From letterForExpression e
			Left Join charToReplaceCTE r
			On e.Expression = r.CharToReplace
			And e.ID = r.ID	
	),
	replaceCharsCTE As
	(
		Select 
			m.ID
			,Rn
			,Expression
			,ReplaceWith
		 From matchLettersCTE m
		 Join charToReplaceCTE r
		 On m.RowNumCharToReplace = r.RowNumReplaceWith
		 And m.ID = r.ID
		Where m.Status = 1
	),
	translateCTE AS
	(
		Select ID,Rn,ReplaceWith  From replaceCharsCTE
		Union All
		Select ID,Rn,Expression From  matchLettersCTE Where Status = 0
	)
	Select @OutputString = x.TranslatedData
	From(
	Select 
		ID
		,TranslatedData = Replace(Replace(Stuff((Select ' '+ Cast( ReplaceWith As Varchar(20))
								From translateCTE t2 Where t1.ID = t2.ID 
								Order By t2.ID,t2.Rn
								For XML PATH('')),1,1,''),' ' ,''),' ',' ')

	from translateCTE t1
	group by ID)x
	Join inputCTE i
	On i.ID = x.ID
	-- Return the result of the function
	RETURN @OutputString

END

Our transalate method is case insensitive. We can invoke it as under

Select
	 PlayerLastName
	 ,dbo.Translate(PlayerLastName,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ZYXWVUTSRQPONMLKJIHGFEDCBA') TranslateExample 
From tblPlayers;

/*Result*/
PlayerLastName	TranslateExample
Raman	IZNZM
Kadir	PZWRI
Nadir	MZWRI
Das	WZH
Punchkar	KFMXSPZI
Fateman	UZGVNZM
Gajani	TZQZMR
Hariharan	SZIRSZIZM
Ikat	RPZG
Jwar	QDZI

OR

Select dbo.Translate('ABCD     EFGH','ABCDEFGH','abcdefgh') Example

/*Result*/
Example
abcd     efgh

Hope this helps.The translate function is attached.Thanks for reading