sql - SQL Server 如何计算一个字符串的所有三元组

Question

我想在 SQL Server 中计算和统计一个字符串的所有三元组。

例如，如果字符串是 hello 我想要以下输出:

Trigram Count------- -----hel     1ell     1llo     1lo-     1

Wikipedia page on n-gram

Answer 1

借用 Ed 和 Martin 的话，我认为这是一个正确的实现:

declare @string varchar(max) = 'here kitty kitty' 

SET @string = replace(@string, ' ', '-') --Wikipedia says this should be underscore, not dash
;with n as 
( 
    SELECT 1 AS i 
    UNION ALL 
    SELECT i + 1  
    FROM n 
    WHERE i < (LEN(@string)-2) 
) 
select SUBSTRING(@string, i, 3) AS Trigram, COUNT(*) AS Count 
from n 
group by SUBSTRING(@string, i, 3) 
option (maxrecursion 0)