[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 的邏輯後，發現自己搞得太複雜，邊拆編組合字串就可以完成。

參考資料：
• TSQL Challenge 3
• Winner Solutions