[Challenge] 找出句子中的回文字

This challenge is to find the palindromic words from sentence(s). Palindrome is a word reading the same backward as forward. While finding the palindromic words from the sentence(s), the noise words should be removed if any is present in the sentence. There is a table "Noise" that defines Noise words

For e.g. MADAM is a palindrome but MADAM! is not. So we need to first remove noise character "!" from MADAM!

There are 2 tables in challenge. One contains the data from which we need to find the Palindromes and another contains noise words.

• Sample Data

Table - Sentence

ID Sentence
-- -------------------------------
1  Hello Madam! how are you Madam?
2  She peep through the window
3  This is without any Palindrome

Table - Noise

ID Noise
-- -----
1  ?
2  !

• Expected Results

ID Sentence                  PalindromeFound PalandromicWords FoundAt
-- ------------------------------- --------------- ---------------- -------------
1  Hello Madam! how are you Madam? 2         Madam      Position : 2,6
2  She peep through the window    1         peep          Position : 2
3  This is without any Palindrome  0         NULL          NULL

• Rules
1. ID should be sorted in Ascending Order.
2. The program should run in SQL SERVER 2005 and above.
3. If no palindrome found in the sentence, then PalindromeFound column will be 0,PalandromicWords and FoundAt column will be NULL.
4. 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 @Sentence TABLE(ID INT IDENTITY, Sentence VARCHAR(1000))
INSERT INTO @Sentence(Sentence)
SELECT 'Hello Madam! how are you Madam?' UNION ALL 
SELECT 'She peep through the window' UNION ALL 
SELECT 'This is without any Palindrome'

DECLARE @Noise TABLE(ID INT IDENTITY, Noise VARCHAR(10))
INSERT INTO @Noise(Noise)
SELECT '?' UNION ALL 
SELECT '!'

; 
WITH CTE AS
(
  SELECT ID , Sentence , 1 AS StartPos , CHARINDEX(SPACE(1),Sentence) AS EndPos
  FROM @Sentence
  UNION ALL
  SELECT ID , Sentence , EndPos + 1 , CHARINDEX(SPACE(1),Sentence,EndPos + 1)
  FROM CTE
  WHERE EndPos > 0
)
, 
CTE2 AS
(
  SELECT T.ID , T.StringCount , ISNULL(REPLACE(String,Noise,''),String) AS ClearNoise
  FROM
    (
      SELECT
        ID ,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY StartPos) AS StringCount ,
        SUBSTRING
          (
            Sentence ,
            StartPos ,
            ISNULL(NULLIF(EndPos,0),LEN(Sentence) + 1) - StartPos
          ) AS String
      FROM CTE
    ) AS T
    LEFT JOIN @Noise AS N ON CHARINDEX(N.Noise,T.String) > 0
)
,
CTE3 AS
(
  SELECT * 
  FROM CTE2
  WHERE ClearNoise = REVERSE(ClearNOISE)
)
SELECT 
  S.ID , 
  S.Sentence , 
  ISNULL(T.PalindromeFound,0) AS PalindromeFound , 
  T.PalandromicWords , 
  T.FoundAt
FROM @Sentence AS S
  LEFT JOIN
    (
      SELECT 
        T1.ID , 
        T1.ClearNoise AS PalandromicWords ,
        COUNT(T1.ID) AS PalindromeFound ,
       'Position :' + 
        STUFF
          (
            (
            SELECT ',' + CAST(T2.StringCount AS varchar(2))
            FROM CTE3 AS T2
            WHERE T1.ID = T2.ID AND T1.ClearNoise = T2.ClearNoise
            ORDER BY T2.StringCount
            FOR XML PATH('')
            ) ,
            1 ,
            1 ,
            ''
          ) AS FoundAt
      FROM CTE3 AS T1
      GROUP BY T1.ID , T1.ClearNoise
    ) AS T ON S.ID = T.ID
ORDER BY ID

參考資料：
• TSQL Beginners Challenge 16 - Find the palindromic words from the sentence(s)