Solution to TSQL Beginners Challenge 15

WITH  DelimitSpace(ID, RowID, Word, string, [Original Sentence], ReverseSentence)
      AS (SELECT   ID,
                   1,
                   CASE WHEN CHARINDEX(' ', Sentence) = 0
                        THEN Sentence
                        ELSE LEFT(Sentence,
                                  CHARINDEX(' ', Sentence) - 1)
                   END,
                   CASE WHEN CHARINDEX(' ', Sentence) = 0
                        THEN Sentence
                        ELSE RIGHT(Sentence,
                                   LEN(Sentence) - CHARINDEX(' ',Sentence))
                   END,
                   Sentence,
                   CONVERT(VARCHAR(1000), CASE WHEN CHARINDEX(' ',Sentence) = 0
                                               THEN Sentence
                                               ELSE LEFT(Sentence,
                                                      CHARINDEX(' ',Sentence) - 1)
                                          END)
          FROM     @t
          UNION ALL
          SELECT   T1.ID,
                   RowID + 1,
                   CASE WHEN CHARINDEX(' ', string) = 0 THEN string
                        ELSE LEFT(string, CHARINDEX(' ', string) - 1)
                   END,
                   CASE WHEN CHARINDEX(' ', string) = 0 THEN string
                        ELSE RIGHT(string,
                                   LEN(string) - CHARINDEX(' ', string))
                   END,
                   [Original Sentence],
                   CONVERT(VARCHAR(1000), CASE WHEN CHARINDEX(' ',string) = 0
                                               THEN string
                                               ELSE LEFT(string,CHARINDEX(' ',string) - 1)
                                          END + ' ' + ReverseSentence)
          FROM     @t T1
          INNER JOIN DelimitSpace T2
                   ON T1.ID = T2.ID
          WHERE    Word <> string)
 SELECT   ID,
          [Original Sentence],
          ReverseSentence AS [Reversed Sentence]
 FROM     DelimitSpace T1
 WHERE    NOT EXISTS ( SELECT  1
                       FROM    DelimitSpace T2
                       WHERE   T1.ID = t2.ID AND
                               T2.RowID > t1.RowID )
 ORDER BY ID

Tags: