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.

Hi,

no there is no quivalent for the translate function.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Well, it is "easy" enough to replicate. Easy being relative of course :) You could use the CLR in 2005, which would probably be my suggestion, but another way is to stack replaces and substrings.

I of course no nothing about Oracle, so I used the following as a guide:

http://www.techonthenet.com/oracle/functions/translate.php

--translate('1tech23', '123', '456); would return '4tech56'
--translate('222tech, '2ec', '3it'); would return '333tith'

declare @.value varchar(100)
declare @.replace varchar(3) --you could support many many more if you want
--I have nested 200+ replace commands and still get
--snappy enough results
declare @.replaceWith varchar(3)

set @.value = '1tech23'
set @.replace = '123'
set @.replaceWith = '456'

select replace(replace(replace(@.value,substring(@.replace,1,1),substring(@.replaceWith,1,1)),substring(@.replace,2,1),substring(@.replaceWith,2,1)),substring(@.replace,3,1),substring(@.replaceWith,3,1))

Your example was:

set @.value = 'entertain'
set @.replace = 'et'
set @.replaceWith = 'ab'

select replace(replace(replace(@.value,substring(@.replace,1,1),substring(@.replaceWith,1,1)),substring(@.replace,2,1),substring(@.replaceWith,2,1)),substring(@.replace,3,1),substring(@.replaceWith,3,1))

This returns:

anbarbain

If you aren't making heavy duty use of this, a T-SQL function will work to genericise it:

create function dbo.translate
(
@.value varchar(max),
@.replace varchar(3),
@.replaceWith varchar(3)
) returns varchar(max) as

begin
return (replace(replace(replace(@.value,substring(@.replace,1,1),substring(@.replaceWith,1,1)),substring(@.replace,2,1),substring(@.replaceWith,2,1)),substring(@.replace,3,1),substring(@.replaceWith,3,1)))
end
go

select dbo.translate('entertain', 'et','ab')
go

Just code as many replace/substring command pairs as you allow by the size of the replace parameter.