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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy