Archive

Archive for April, 2011

SQL Server TRANSLATE function

Here’s my script to create a SQL Server version of oracle translate function, I have tried to create my own version of translate function which is available as a built in function of ORACLE. Try and provide your valuable suggestions through comments,

DECLARE @data VARCHAR (50)

DECLARE @datatoreplace VARCHAR (50)

DECLARE @replacewithdata VARCHAR (50)

SET @data = 'dinesh'

SET @datatoreplace = 'din'

SET @replacewithdata = 'raj'

DECLARE @length INT

DECLARE @position INT

DECLARE @Replaceddata VARCHAR (50)

SET @length = Len( @datatoreplace)

IF Len (@datatoreplace) <> Len(@replacewithdata )

BEGIN

RAISERROR (N'The Variables @datatoreplace and @replacewithdata should be same in length, Translation cannot be done ', 16,1 );

GOTO EndSP

END

SET @position = 1

WHILE ( @position <= @length )

BEGIN

SET @Replaceddata = Replace (@data, LEFT(@datatoreplace , 1), LEFT(@replacewithdata, 1))

SET @datatoreplace = RIGHT (@datatoreplace, Len( @datatoreplace) - 1)

SET @replacewithdata = RIGHT (@replacewithdata, Len(@replacewithdata ) - 1)

SET @position = @position + 1

SET @data = @Replaceddata

END

SELECT @Replaceddata

ENDSP:

you can also even create a SQL Server Function with the above T-SQL code as shown below,

 

CREATE FUNCTION dbo.Translate (@data            AS VARCHAR(50),
@datatoreplace   AS VARCHAR(50),
@replacewithdata AS VARCHAR(50))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @length INT
DECLARE @position INT
DECLARE @Replaceddata VARCHAR(50)

SET @length = Len(@datatoreplace)

IF Len(@datatoreplace) <> Len(@replacewithdata)
BEGIN
RETURN N'The Variables @datatoreplace and @replacewithdata should be same in length, Translation cannot be done '
END

SET @position = 1

WHILE ( @position <= @length )
BEGIN
SET @Replaceddata = Replace(@data, LEFT(@datatoreplace, 1), LEFT(@replacewithdata, 1))
SET @datatoreplace = RIGHT(@datatoreplace, Len(@datatoreplace) - 1)
SET @replacewithdata = RIGHT(@replacewithdata, Len(@replacewithdata) - 1)
SET @position = @position + 1
SET @data = @Replaceddata
END

RETURN @Replaceddata
END;

 

SELECT dbo.TRANSLATE ('Dinesh','Din','Raj')

 

OUTPUT:

image

Advertisements
%d bloggers like this: