This is what we use:
ALTER FUNCTION dbo.Translate
( @SourceVARCHAR(8000)
, @ReplaceCharOrder VARCHAR(8000)
, @ReplaceWithCharOrderVARCHAR(8000)
)
RETURNS VARCHAR(8000) AS
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Object Name:dbo.Translate
Author:UB for DCF, on August05, 2008
Purpose:Like TRANSLATE function in Oracle. Charecter-wise replace in source string with given charecters.
Input:
Output:returns @Translated_Source string
Version:1.0 as of 08/05/2008
Modification:
Execute:SELECT dbo.Translate('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', '1234567890', '0987654321')
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
BEGIN
--
--Validate input
--
IF@SourceIS NULL
RETURN NULL
IF @Source= ''
RETURN ''
IF @ReplaceCharOrder IS NULLOR
@ReplaceCharOrder = ''
RETURN @Source
IF@ReplaceWithCharOrder IS NULL
RETURN 'Invalid parameters in function call dbo.Translate'
--
--Variables used
--
DECLARE @Curr_Pos_In_SourceINT
, @Check_Source_Str_LenINT
, @nth_sourceVARCHAR(1)
, @Found_MatchINT
, @Translated_SourceVARCHAR(8000)
, @Match_In_ReplaceWithVARCHAR(1)
--
--Assign starting values for variables
--
SELECT @Curr_Pos_In_Source= 1
, @Check_Source_Str_Len= LEN(@Source)
, @Translated_Source= ''
--
--Replace each charecter with its corrosponding charecter from @ReplaceWithCharOrder
--
WHILE @Curr_Pos_In_Source <= @Check_Source_Str_Len
BEGIN
--
--Get the n'th charecter in @Source
--
SELECT @nth_source = SUBSTRING(@Source, @Curr_Pos_In_Source, 1)
--
--See if there is a matching character for @nth_source in the @ReplaceCharOrder String, then replace it with
--corrosponding character in @ReplaceWithCharOrder String. If not..go to next n'th character in @Source
--If a match is found in @ReplaceCharOrder but no corrosponding character in @ReplaceWithCharOrder
--then, replace it with '' (nothing)
--Store the resultant string in a separate variable
--
SELECT @Found_Match = CHARINDEX(@nth_source, @ReplaceCharOrder COLLATE SQL_Latin1_General_CP1_CS_AS)
IF @Found_Match > 0
BEGIN
--
--Finding corrosponding match in the @Found_Match'th position in @ReplaceWithCharOrder
--if not found then replace it with '' (nothing)
--
SELECT @Match_In_ReplaceWith = SUBSTRING(@ReplaceWithCharOrder, @Found_Match, 1)
--
--Now replace @nth_source with @Match_In_ReplaceWith and store it in @Translated_Source
--
SELECT @Translated_Source = @Translated_Source + @Match_In_ReplaceWith
END
ELSE
BEGIN
--
--No match found in @ReplaceCharOrder
--
SELECT @Translated_Source = @Translated_Source + @nth_source
END
--
--Increment the current position for loop
--
SELECT @Curr_Pos_In_Source = @Curr_Pos_In_Source + 1
END
RETURN @Translated_Source
END
/*
TESTING:
SELECT dbo.Translate('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', 'abcdefghijklmnopqrstuvwxyz098765432')
SELECT dbo.Translate('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', '0123456789', '9876543210')
SELECT dbo.Translate('', '', '')
SELECT dbo.Translate('NOMENCLATURE', 'ABCLE', '123')
*/