星期五, 11月 02, 2012

[Challenge] 反轉字串

Beyond Relational TSQL Challenge 3

挑戰不利用 REVERSE() 函數來反轉字串,REVERSE() 函數本身沒有任何問題,實務上仍是利用它來反轉字串,在此只是來考驗對 T-SQL 結果集(set based operations)的操作。

不單獨反轉單一字串,反轉 Table 欄位中的全部資料。
  • Sample Data
ID          data
----------- --------------------
1           Jacob
2           Sebastian
  • Expected Result
id          data
----------- --------------------
2           naitsabeS
1           bocaJ
  • 規則
    1. 不能利用使用者自訂函數,且只能用單一個 T-SQL語法
    2. 請確實利用用提供的 Smaple Data 來作答,方便檢查結果是否正確
    3. 沒有 SQL Server 的版本限制

  • 個人解法
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'

;
WITH CTE AS
(
  SELECT ID , Data ,1 AS Location
  FROM @T
  UNION ALL
  SELECT ID , Data , Location + 1
  FROM CTE
  WHERE Location < LEN(Data)
)
, CTE2 AS
(
  SELECT ID , SUBSTRING(Data,Location,1) AS Data , Location
  FROM CTE
)
, CTE3 AS
(
  SELECT ID , CAST(Data AS VARCHAR(100)) AS Data , Location , 1 AS Counts
  FROM CTE2 
  WHERE Location = 1
  UNION ALL
  SELECT T2.ID , CAST( RTRIM(T2.Data) + T3.Data AS VARCHAR(100)) , T2.Location , T3.Counts + 1
  FROM CTE2 AS T2 
    JOIN CTE3 AS T3 ON T2.ID = T3.ID AND T2.Location = T3.Counts + 1
)
SELECT T4.ID , T4.Data
FROM CTE3 AS T4
  JOIN
    (
      SELECT ID , MAX(Counts) AS Counts
      FROM CTE3
      GROUP BY ID
    ) AS T5 ON T4.ID = T5.ID AND T4.Counts = T5.Counts
ORDER BY T4.ID DESC

  • 參考 Solutions Winner 解法後的改良
DECLARE @t TABLE( ID INT IDENTITY, data NVARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'

; 
WITH CTE AS
(
  SELECT 
    ID , 
    Data , 
    CAST('' AS NVARCHAR(10)) AS String , 
    LEN(Data) AS Pos
  FROM @t
  UNION ALL
  SELECT 
    ID , 
    Data , 
    CAST(RTRIM(String) + SUBSTRING(Data , Pos , 1 ) AS NVARCHAR(10)) , 
    Pos - 1
  FROM CTE
  WHERE Pos > 0
)
SELECT ID , string AS Data
FROM CTE 
WHERE Pos = 0 
ORDER BY ID DESC
原本解法是把字串拆開成 Data,再去針對 Data 跑 CTE , 了解 Solutions 的邏輯後,發現自己搞得太複雜,邊拆編組合字串就可以完成。

沒有留言:

張貼留言