[Challenge] 反轉句子內的單字

Beyond Relational TSQL Beginners Challenge 15

This challenge is to reverse the order of words within a sentence. Most of us must be familiar with reversing the order of letters within a word.

This challenge invites you to take a sentence and reverse the order of words within it. For example, if the original sentence is "Hello! how are you?", the reversed version should be "you? are how Hello!".

• Sample Data

ID Sentence
-- -------------------------------------
1  This is T-SQL Beginners Challenge #15
2  I am a challenge competitor

• Expected Results

ID Original Sentence                     Reversed Sentence
-- ------------------------------------- -------------------------------------
1  This is T-SQL Beginners Challenge #15 #15 Challenge Beginners T-SQL is This
2  I am a challenge competitor           competitor challenge a am I

• Rules
1. ID should be sorted in Ascending Order.
2. The program should run in SQL SERVER 2005 and above.
3. The output should be in the same way as it has been shown. Column names should be exactly the same and the result must be sorted in Ascending order of ID.

• 個人解法

DECLARE @t TABLE(ID INT IDENTITY, Sentence VARCHAR(1000))
INSERT INTO @t(Sentence)
SELECT 'This is T-SQL Beginners Challenge #15' UNION ALL
SELECT 'I am a challenge competitor'

;
WITH CTE AS
(
  SELECT 
    ID , 
    Sentence , 
    1 AS StartPos ,
    CHARINDEX(SPACE(1),Sentence) AS EndPos ,
    CAST
      (
        SUBSTRING
          (
            Sentence ,
            1 , 
            CHARINDEX(SPACE(1),Sentence)-1
          ) 
        AS VARCHAR(100)
      ) AS String
  FROM @t
  UNION ALL
  SELECT 
    ID , 
    Sentence , 
    EndPos + 1 ,
    CHARINDEX(SPACE(1),Sentence,EndPos+1) ,
    CAST
      (
        SUBSTRING
          (
            Sentence ,
            EndPos + 1 ,
            ISNULL
              (
                NULLIF
                  (
                    CHARINDEX(SPACE(1),Sentence,EndPos + 1) , 
                    0
                  ) 
                ,  
                LEN(Sentence) + 1
              )
            - ([EndPos] + 1) 
          )
        + SPACE(1) + RTRIM(String)
        AS VARCHAR(100)
      )
 FROM CTE
 WHERE [EndPos] > 0
)
SELECT ID , Sentence AS [Original Sentence], String AS [Reversed Sentence]
FROM CTE 
WHERE EndPos = 0
ORDER BY ID

參考資料：
• TSQL Beginners Challenge 15 - Reverse the order of words within a sentence