Home > T-SQL > SQL Server TRANSLATE function

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
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: