Equivalent to Oracle TRANSLATE function in Microsoft SQL Server

$0
=
$0
+
$0
No specific Bitcoin Bounty has been announced by author. Still, anyone could send Bitcoin Tips to those who provide a good answer.
0

What is the equivalent to Oracle PL/SQL TRANSLATE function in Microsoft SQL Server T-SQL?

TRANSLATE returns string with all occurrences of each character in from_string replaced by its corresponding character in to_string, both of these strings are being passed as parameters to TRANSLATE. For example,

TRANSLATE('big brown cow', 'abc', 'xyz')  /* this would return 'yig yrown zow' */

This function is useful in many situations, for example, replacing letters in phone numbers with corresponding digits, and in many other situations. I am assuming there is no built-in equivalent, so we might need to create a custom T-SQL function that would work like Oracle's TRANSLATE?

1 Answer - total earned - 0 BTC ($0 USD)

1
=
0
=
$0
Everyone, even non-registered users could send Bitcoin Tips to YOU if they are inspired by your answer!

This T-SQL user-defined function would work exactly the same as Oracle's PL/SQl TRANSLATE function. Please note that by default it is case-insensitive, i.e. 'A' and 'a' work the same. However, if your instance of SQL Server is made case-sensitive by your DBA, then this whole function would become case-sensitive.

/* @search and @replacement params must have EXACTLY the same number of chars, and together they represent the desired string transformation  */

create function dbo.Translate
(
  @source varchar(8000),
  @search varchar(8000),
  @replacement varchar(8000)
)
returns varchar(8000)
as
begin

declare @i int, @iMax int

set @iMax = len(@search)
set @i = 1

while @i <= @iMax
begin
  set @source = replace(@source, substring(@search, @i, 1), substring(@replacement, @i, 1))
  set @i = @i + 1
end

return @source

end
go

Testing:

select dbo.Translate('big brown cow', 'abc', 'xyz')

-------------
yig yrown zow

(1 row(s) affected)
SEND BITCOIN TIPS

I made a change, with this version you can work with UPPER and lower cases as you wish :

 create function dbo.translate
(
  @source varchar(8000),
  @search varchar(8000),
  @replacement varchar(8000)
)
returns varchar(8000)
as
begin

declare @i int, @iMax int

set @iMax = len(@search)
set @i = 1

while @i <= @iMax
begin
  set @source  = replace(@source collate latin1_general_CS_AS, substring(@search collate latin1_general_CS_AS, @i, 1), substring(@replacement collate latin1_general_CS_AS, @i, 1))
  set @i = @i + 1
end

return @source

end
go
1

Post Answer


Will Bitcoin Tipping become a new Internet Culture?


Please share our story! THANK YOU!