sql - sql server 中每个单词在一个字符串中重复多少次？

Question

我已经声明了一个字符串，如下所示:

Declare @string
Set @string = 'this line is like this because this is repeated these many times in this line'

我试图找出每个单词重复了多少次。我期待这样的结果

Word   Number
this    x times
line    y times
is      z times

等等……

帮我写代码。任何帮助，将不胜感激。提前谢谢你。

编辑:

到目前为止，我已经找到了替换特定字母的数字单词。

这是它的代码

SELECT Len(@string) - Len(Replace(@string, 'x', '')).

任何解释连同提供的代码将不胜感激。谢谢。

Answer 1

;WITH splitString(val) AS       
(
    -- convert the string to xml, seperating the elements by spaces
    SELECT    CAST('<r><i>' + REPLACE(@string,' ','</i><i>') + '</i></r>' AS XML)
)
SELECT  [Key],
        COUNT(*) [WordCount]
FROM    (   -- select all of the values from the xml created in the cte
            SELECT  p.value('.','varchar(100)') AS [Key]
            FROM    splitString
                    CROSS APPLY val.nodes('//i') t (p)) AS t
GROUP BY [Key]

如果你想获得所有技术..

;WITH splitString(val) AS       
(
    -- convert the string to xml, seperating the elements by spaces
    SELECT    CAST('<r><i>' + REPLACE(@string,' ','</i><i>') + '</i></r>' AS XML)
)
SELECT  Word,
        CAST(COUNT(*) AS VARCHAR) 
            + (CASE WHEN COUNT(*) = 1 THEN ' time' ELSE ' times' END) AS  Number
FROM    (   -- select all of the values from the xml created in the cte
            SELECT  p.value('.','varchar(100)') AS Word
            FROM    splitString
                    CROSS APPLY val.nodes('//i') t (p)) AS t
GROUP BY Word