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,
QRID Login
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?
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
User rating:
User rating:
User rating:
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
FavScripts.com is a free tool to save your favorite scripts and commands, then quickly find and copy-paste your commands with just few clicks.
Boost your productivity with FavScripts.com!
