January 3, 2002 at 4:41 am
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('SÃO', 'Ã', 'A')
It will return 'SAO'.
I think it might be useful.
CREATE FUNCTIONTRANSLATE
(@string_inVARCHAR(8000),
@string_1VARCHAR(8000),
@string_2VARCHAR(8000))
RETURNSVARCHAR(8000)
AS
BEGIN
DECLARE@string_outVARCHAR(8000),
@stringCHAR(1),
@iSMALLINT,
@posSMALLINT
DECLARE@listTABLE (indxSMALLINT,
stringCHAR(1),
transBIT)
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
SETstring = SUBSTRING(@string_2, @i, 1),
trans = 1
WHEREstring = SUBSTRING(@string_1, @i, 1)
ANDtrans = 0
SELECT@i = @i + 1
END
SELECT@pos = MAX(indx),
@i = 1
FROM@list
WHILE@i <= @pos
BEGIN
SELECT@string = string
FROM@list
WHEREindx = @i
SELECT@string_out = @string_out + @string
SELECT@i = @i + 1
END
RETURN@string_out
END
Please, post any suggestions.
TIA
Mateus Espadoto
January 3, 2002 at 4:44 am
Isn't this the same as the T_SQL function REPLACE?
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 3, 2002 at 5:06 am
Actually no.
Take the following example:
SELECT REPLACE ('ABCDE', 'BD', 'CE')
The result will be 'ABCDE', because the 'BD' string doesn't exists in the 'ABCDE' string.
If you use TRANSLATE:
SELECT TRANSLATE ('ABCDE', 'BD', 'CE')
It will return 'ACCEE', because the TRANSLATE function will replace each 'B' it finds with 'C's and each 'D' with 'E's.
January 3, 2002 at 5:12 am
Gotya - could be a useful distinction.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 3, 2002 at 5:24 am
Have you added this to our script library?
Andy
January 3, 2002 at 6:16 am
Not yet.
I will.
January 3, 2002 at 11:06 am
Great! Better chance someone will stumble across it there.
Andy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply