[Challenge] 字串中計算 HTML Tag 發生次數

This challenge involves counting the number of occurrences of HTML tags in a given list of strings. You can assume that there will be only valid HTML tags in the input strings. The output should display tags in ascending order.

• Sample Data

ID  HtmlText
-- ------------------------------------------------------------------------
1    ＜html＞＜body＞＜font＞This is challenge #18＜/Font＞＜font＞＜/Font＞＜/Body＞＜/Html＞

• Expected Results

ID TagNamesOccurance 
-- --------------------------------------------------------------
1  Body(Found: 1 time), Font(Found: 2 times), Html(Found: 1 time)

• 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, HtmlText VARCHAR(Max))
INSERT INTO @t(HtmlText)
SELECT 'This is challenge #18'

; 
WITH CTE AS
(
  SELECT ID , HtmlText , 
         CHARINDEX('<',HtmlText) AS PosStart , 
         CHARINDEX('>',HtmlText) AS PosEnd
  FROM @t
  UNION ALL
  SELECT ID , HTMLText , 
         CHARINDEX('<',HtmlText,PosEnd + 1) , 
        CHARINDEX('>',HtmlText,PosEnd + 1)
  FROM CTE
  WHERE PosStart > 0 
        AND PosEnd > 0
)
SELECT 
  ID , 
  'Body(Found: ' + CAST(Body AS varchar) + ') , ' +
  'Font(Found: ' + CAST(Font AS varchar) + ') , ' + 
  'Html(Found: ' + CAST(Html AS varchar) + ')' AS TagNamesOccurance 
FROM
  (
    SELECT 
        T.ID , 
        T.String , 
        CAST
          ( 
            COUNT(T.String) AS varchar(2)
          ) +  
        CASE 
          WHEN COUNT(T.String) > 1 THEN ' times' 
          ELSE ' time' 
        END AS Data
    FROM
      (
        SELECT 
          ID ,
          SUBSTRING
            (
              HtmlText,
              PosStart + 1 ,
              PosEnd - (PosStart + 1)
            ) AS String
        FROM CTE
        WHERE PosStart > 0 
            AND PosEnd > 0 
      ) AS T
    GROUP BY T.ID , T.String
  ) AS P
PIVOT
  (
    MAX(Data) FOR String IN ([Body],[Font],[Html])
  ) AS PV
ORDER BY ID

參考資料：
• TSQL Beginners Challenge 18 - Count the total occurrences of HTML tags in the given string
• Solutions - 應用到 FOR XML 和 利用長度判斷字串內固定字串出現次數 的解法