[Challenge] 計算字元在字串中出現次數

Beyond Relational TSQL Beginners Challenge 14

This challenge is all about counting the number of occurrence of characters in a string using a SET based query. The task is to list all the characters and count of that character within the given string.

• 資料來源

Data
----
12
xx

• 產生結果

Data Chars NumberOfOccurance
---- ----- -------------------
12  1    1 appears (1) times
12  2    2 appears (1) times
xx  x    x appears (2) times

• 規則
1. Results must be sorted in ascending order of Data and then by Character.
2. The output should be in the same way as it has been shown.

• 個人解法

DECLARE @T TABLE(Data VARCHAR(MAX))
INSERT INTO @T
SELECT '12' UNION ALL
SELECT 'xx' 
SELECT * FROM @T

;
WITH CTE AS
(
  SELECT Data , 1 AS Location
  FROM @T
  UNION ALL
  SELECT Data , Location + 1
  FROM CTE
  WHERE Location < LEN(Data)
)
SELECT 
  T.Data , 
  T.Chars , 
  T.Chars + ' appears (' + CAST(COUNT(*) AS varchar(10)) + ') times' AS NumberOfOccurance
FROM
(
  SELECT 
    Data,
    SUBSTRING(Data,Location,1) AS Chars
  FROM CTE 
) AS T
GROUP BY T.Data , T.Chars
ORDER BY T.Data , T.Chars

• Solutions 內特殊解法

利用 spt_values system table 來達成需求

DECLARE  @T TABLE(Data VARCHAR(MAX))
INSERT INTO @T
SELECT '12' UNION ALL
SELECT 'xx';

WITH strCTE(Data,chars) 
AS
(
  SELECT DISTINCT Data , SUBSTRING(Data,Number,1) AS chars 
  FROM @T T 
    INNER JOIN master..spt_values sv ON Number <= LEN(T.Data)
  WHERE number > 0
          AND [type] = 'P'
)
SELECT 
  Data ,
  chars ,
  chars + ' appears (' + CAST((LEN(Data) - LEN(REPLACE(Data, chars,''))) AS varchar(max)) + ') times' AS NumberOfOccurance
FROM strCTE ;

參考資料
• TSQL Beginners Challenge 14 - Count the number of occurrence of characters in the string