Technical Article

Port from Oracle's TRANSLATE Function

,

Here is a port from the Oracle's TRANSLATE function to T-SQL.

It gets three arguments: a string to be searched, a string with a set of characters to be found and replaced, and another set of characters as the replacements.

Example:

SELECT dbo.TRANSLATE('ABCDE', 'BD', 'CE')

It will return 'ACCEE'.

CREATE FUNCTION TRANSLATE
( @string_in VARCHAR(8000),
@string_1 VARCHAR(8000),
@string_2 VARCHAR(8000))
RETURNS VARCHAR(8000)

AS
BEGIN

DECLARE @string_out VARCHAR(8000),
@string CHAR(1),
@i SMALLINT,
@pos SMALLINT

DECLARE @list TABLE ( indx SMALLINT,
string CHAR(1),
trans BIT)

IF LEN(@string_1) != LEN(@string_2)
BEGIN

SELECT @string_out = NULL
RETURN @string_out

END


SELECT @string_out = '',
@i = 1,
@pos = 0

WHILE @i <= LEN(@string_in)
BEGIN
INSERT INTO @list
VALUES (@i,
SUBSTRING(@string_in, @i, 1),
0)

SELECT @i = @i + 1
END

SELECT @i = 1

WHILE @i <= LEN(@string_1)
BEGIN

UPDATE @list
SET string = SUBSTRING(@string_2, @i, 1),
trans = 1
WHERE string = SUBSTRING(@string_1, @i, 1)
AND trans = 0

SELECT @i = @i + 1
END

SELECT @pos = MAX(indx),
@i = 1
FROM @list

WHILE @i <= @pos
BEGIN

SELECT @string = string
FROM @list
WHERE indx = @i

SELECT @string_out = @string_out + @string

SELECT @i = @i + 1

END

RETURN @string_out

END

Rate

3 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (2)

You rated this post out of 5. Change rating