Oracle Translate function equivalent in SQL Server

Hi

I want to know the equivalent of the Oracle translate function in SQL Server.

eg : select translate('entertain', 'et', 'ab') from dual.

I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.

Please let me know if there is some other equivalent function in SQL Server

thanks.CASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp) works wonders.

-PatP|||oh, i would just love to see how CASE works in this, er, um, case

care to share the example for TRANSLATE('entertain', 'et', 'ab'), pat?|||No. I'm tired, cranky, and trying to help. What are you doing to help?

-PatP|||What are you doing to help?subtly trying to inform the original poster that pursuing CASE may be a waste of time until he sees an actual example (which i am having a hard time conceiving)

i merely tried to match the degree of subtlety in my reply to yours

:)|||Maybe I am being a thicky pants but do nested REPLACEs not do the trick?|||thicky pants?

yes, that's the way i'd do it, as many nested REPLACE functions as characters to be translated

here's a classic example of TRANSLATE being used for simple encryption --

SELECT TRANSLATE(mycolumn,'abcdefghijklmnopqrstuvxyz', '5869413270plokij^#jm![edxc')|||Gotta love that Friday Feeling:
CREATE FUNCTION dbo.Thicky_Pants
(
@.Input AS VarChar(1000),
@.Find AS VarChar(100),
@.Replace AS VarChar(100)
)
RETURNS VarChar(1000)
AS
BEGIN

DECLARE @.i AS TinyInt

SELECT @.i = 1

WHILE @.i <= LEN(@.Find) BEGIN

SELECT @.Input = REPLACE(@.Input, SUBSTRING(@.Find, @.i, 1), SUBSTRING(@.Replace, @.i, 1))
SELECT @.i = @.i + 1

END
RETURN @.Input
END
GO

DECLARE @.String AS VarChar(1000)

SELECT @.String = 'pootle_flump'
SELECT @.String = dbo.Thicky_Pants(@.String, 'pt', 'xz')

PRINT @.String
xoozle_flumx|||here's a classic example of TRANSLATE being used for simple encryption --

SELECT TRANSLATE(mycolumn,'abcdefghijklmnopqrstuvxyz', '5869413270plokij^#jm![edxc')

Unless you are developing an application to write cryptograms for the Sunday paper, I hope nobody would use such an algorithm.

TRANSLATE is one of the goofier built-in functions in Oracle. The laser was once described as "a solution in search of a problem". Maybe someday there will be a practical use for TRANSLATE as well. In 10 years of SQL Server programming I've never found a need for such a function.|||In 10 years of SQL Server programming I've never found a need for such a function.well, there's your answer -- you need it all the time in oracle :)|||Somehow I've manager to get through a few Oracle projects without using it.