In Oracle/PLSQL, the Translate function replaces a sequence of characters in a string with another set of characters. 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.
For example, translate('challenge', 'aen', 'xyz'); would return 'chxllyzgy'. That is 'a' is replaced by 'x', 'e' is replaced by 'y' and 'n' by 'z'
The challenge is to implement similarly functionality in TSQL, that works similar to TRANSLATE function of ORACLE.
- Sample Data
Data DataToReplace ReplacedWithData ---- ------------- ---------------- Cap cp xy
- Expected Results
OriginalData TranslatedData ------------ -------------- Cap xay
- Rules
- The program should run in SQL SERVER 2005 and above.
- The output should be in the same way as it has been shown.
- TRANSLATE(string,from_str,to_str) 語法
- 個人解法
DECLARE @t TABLE(
Data VARCHAR(20),
DataToReplace VARCHAR(100),
ReplacedWithData VARCHAR(100)
)
INSERT INTO @t
SELECT 'Cap','cp','xy'
;
WiTH CTE1 AS
(
-- CTE1 是為了避免傳入空值，Oracle 會認定空值為 NULL
SELECT
NULLIF(Data,'') AS Data ,
NULLIF(DataToReplace,'') AS DataToReplace ,
NULLIF(ReplacedWithData,'') AS ReplacedWithData
FROM @t
)
,
CTE2 AS
(
-- CTE2 以 Data 的長度為主，利用 CTE 拆出每個字串，並達到資料正規化目的
SELECT
Data , SUBSTRING(Data,1,1) AS String1 ,
DataToReplace , SUBSTRING(DataToReplace,1,1) AS String2 ,
ReplacedWithData , SUBSTRING(ReplacedWithData,1,1) AS String3 ,
1 AS counts
FROM CTE1
UNION ALL
SELECT
Data , SUBSTRING(Data,counts + 1,1) ,
DataToReplace , SUBSTRING(DataToReplace,counts + 1,1) ,
ReplacedWithData , SUBSTRING(ReplacedWithData,counts + 1,1) ,
counts + 1
FROM CTE2
WHERE LEN(Data) >= counts + 1
)
,
CTE3 AS
(
-- 把 CTE2 當成兩個 Table，一個是資料來源，另一個是取代關係表，
-- 利用 LEFT JOIN 搭配 ISNULL() 來判斷最後要呈現的字串
SELECT T1.Data , ISNULL(T2.String3,T1.String1) AS String
FROM CTE2 AS T1
LEFT JOIN CTE2 AS T2 ON T1.String1 = T2.String2
)
-- 利用 FOR XML PATH 來組合字串，一旦原來的資料和組合字串一樣，
-- 表示沒有取代任何字串，要回傳 NULL
SELECT
F.OriginalData ,
IIF(F.OriginalData = F.TranslatedData,NULL,F.TranslatedData) AS TranslatedData
FROM
(
SELECT T1.Data AS OriginalData ,
(
SELECT '' + T2.String
FROM CTE3 AS T2
WHERE T1.Data = T2.Data
FOR XML PATH('')
) AS TranslatedData
FROM CTE3 AS T1
GROUP BY T1.Data
) AS F
沒有留言:
張貼留言