Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL Server 119
#SQLServer 88
Oracle 70
#SQL SERVER 35
BRH 31
SQL Server 2012 29
denali 23
#TSQL 19
TSQL 19
C# 15

Archive · View All
October 2011 31
November 2011 30
September 2011 30
August 2011 18
December 2011 15
July 2011 13
June 2011 8
May 2012 4
April 2012 3
January 2010 3

Day 8: Translate function in Oracle

Sep 21 2011 10:56PM by Niladri Biswas   

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
  3. 	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

Tags: #SQLServer, SQL Server, Oracle,


Niladri Biswas
7 · 21% · 6710
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Day 8: Translate function in Oracle" rated 5 out of 5 by 1 readers
Day 8: Translate function in Oracle , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]