Translate Function:
In Oracle/PLSQL, the translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.
The syntax for the translate function is:
translate( string1, string_to_replace, replacement_string )
string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string - All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.
Eaxmples :
translate('1tech23', '123', '456'); would return '4tech56'
translate('222tech', '2ec', '3it'); would return '333tith'
I have implemented the same for MS SQL Server. The syntax would be similar as of translate in Oracle.
Syntax : Translate(Data,DataToReplace,ReplacedWithData)
Data : Is the string can be of VARCHAR(MAX)
DataToReplace : Is the characters can be of VARCHAR(100)
ReplacedWithData : Is the characters can be of VARCHAR(100)
USE AdventureWorks
GO
IF OBJECT_ID (N
'dbo.Translate'
, N
'FN'
)
IS
NOT
NULL
DROP
FUNCTION
dbo.Translate;
GO
CREATE
FUNCTION
dbo.Translate (@Data
VARCHAR
(
MAX
), @DataToReplace
VARCHAR
(100), @ReplacedWithData
VARCHAR
(100))
RETURNS
VARCHAR
(
MAX
)
BEGIN
DECLARE
@TranslaedData
VARCHAR
(
MAX
)
;
WITH
CTE(PosToReplace,Data,DataToReplace,ReplacedWithData)
AS
(
SELECT
1,
CAST
(@Data
AS
VARCHAR
(
MAX
))
AS
Data,
CAST
(
SUBSTRING
(@DataToReplace,1,1)
AS
VARCHAR
(
MAX
))
AS
DataToReplace,
CAST
(
SUBSTRING
(@ReplacedWithData,1,1)
AS
VARCHAR
(
MAX
))
AS
ReplacedWithData
UNION
ALL
SELECT
C.PosToReplace+1
AS
PosToReplace ,
CAST
(
REPLACE
(C.Data,C.DataToReplace,C.ReplacedWithData)
AS
VARCHAR
(
MAX
))
AS
Data,
CAST
(
SUBSTRING
(@DataToReplace,PosToReplace+1,1)
AS
VARCHAR
(
MAX
))
AS
DataToReplace,
CAST
(
SUBSTRING
(@ReplacedWithData,PosToReplace+1,1)
AS
VARCHAR
(
MAX
))
AS
ReplacedWithData
FROM
CTE C
WHERE
C.PosToReplace <= LEN(@DataToReplace)
)
SELECT
@TranslaedData = C.Data
FROM
CTE C
WHERE
C.PosToReplace = LEN(@DataToReplace)+1
RETURN
@TranslaedData
END
Perhaps these can be modified easily.
I am also including the code snippet so other users can also benifit from it.
Note : This is my first contribution at Gallery. Please let mw know about this if you have any suggestions i am very much open to hear it from you guys.
Thanks,
Hasham
------------------------------------------------------------------------------------------------------------
Above code is not looking for lowercase or uppercase. Below code will work for all.
Thanks,
Krushna
IF OBJECT_ID (N'dbo.fn_translate', N'FN') IS NOT NULL DECLARE @string_1 NVARCHAR(4000) IF (@string_1 IS NULL OR @string_2 IS NULL) DECLARE @string_out NVARCHAR(4000) DECLARE @list TABLE ( indx SMALLINT, IF LEN(@string_1) != LEN(@string_2) SELECT @string_out = '', WHILE @i <= LEN(@string_in) SELECT @i = 1 WHILE @i <= LEN(@string_1) SELECT @i = @i + 1 SELECT @pos = MAX(indx), WHILE @i <= @pos SELECT @string_out = @string_out + @string SELECT @i = @i + 1 RETURN @string_out END GO |