Translator function for SQL Server
CREATE FUNCTION TRANSLATE(@STR VARCHAR(8000),@FSTR VARCHAR(8000),@SSTR VARCHAR(8000))
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @I INT,
@FVAL VARCHAR(1),
@SVAL VARCHAR(2)
SET @I = LEN(@FSTR)
WHILE @I > 0
BEGIN
SET @FVAL = SUBSTRING(@FSTR,@I,1)
SET @SVAL = SUBSTRING(@SSTR,@I,1)
SET @STR = REPLACE(@STR,@FVAL,@SVAL)
SET @I = @I - 1
END
RETURN @STR
END
-- Examples
--Select dbo.TRANSLATE('Bineesh','abcdefghijklmnopqrstuvwxyz','~!@#$%^&*()_+<>?-={}[]\?.')
---Will give output as !*<$${&
--Select dbo.TRANSLATE('!*<$${&','~!@#$%^&*()_+<>?-={}[]\?.','abcdefghijklmnopqrstuvwxyz')
---Again will give output back as bineesh
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @I INT,
@FVAL VARCHAR(1),
@SVAL VARCHAR(2)
SET @I = LEN(@FSTR)
WHILE @I > 0
BEGIN
SET @FVAL = SUBSTRING(@FSTR,@I,1)
SET @SVAL = SUBSTRING(@SSTR,@I,1)
SET @STR = REPLACE(@STR,@FVAL,@SVAL)
SET @I = @I - 1
END
RETURN @STR
END
-- Examples
--Select dbo.TRANSLATE('Bineesh','abcdefghijklmnopqrstuvwxyz','~!@#$%^&*()_+<>?-={}[]\?.')
---Will give output as !*<$${&
--Select dbo.TRANSLATE('!*<$${&','~!@#$%^&*()_+<>?-={}[]\?.','abcdefghijklmnopqrstuvwxyz')
---Again will give output back as bineesh

0 Comments:
Post a Comment
<< Home