在SQL Server中使用LIKE关键字进行排名

Question

I have table like

╔═══════════════════╗
║     Title         ║
╠═══════════════════╣
║     Blogroll      ║
║     Bottom Menu   ║
║     Business      ║
║     Entertainment ║
║     extend        ║
╚═══════════════════╝

我的搜索条件类似于：

WHERE title LIKE '%blogroller%'

显然我在这里没有结果，但是我能找到类似语句结束的计数吗？比如在这个例子中，它超过了8，导致了类似语句失败？

任何提示都将不胜感激。

谢谢。

- Sandip Bantawa

Answer 1

你可以这样做，但需要付出大量的人工努力：

select title,
       (case when title like '%blogroller%' then 10
             when title like '%blogrolle%' then 9
             when title like '%blogroll%' then 8
             . . .
             else 0
        end) as MatchLen
from table t
order by MatchLen desc;

（注：在某些版本的SQL Server中，您可能需要使用子查询来引用MatchLen。）

- Gordon Linoff

---

我认为他正在寻找一个返回匹配字符数量的函数。 - Tim Schmelter

这是我迄今为止得到的最好的结果 - Sandip Bantawa

这是目前为止最好的方法，但你应该通过将搜索词的每个子字符串添加到临时表或分隔符字符串中来进行泛化，然后对其运行查询。如果需要，我可以提供一个示例。 - nikolifish

@nikolifish . . . 我会通过创建递归CTE来概括它，以便提取出模式。如果您或OP感兴趣，请提出另一个问题以获得更一般的解决方案。 - Gordon Linoff

Answer 2

你不能一次性完成它，所以你需要逐个尝试所有选项，但是你可以显著地优化这个过程，这就是SQL的作用。

首先，将模式转换成所有可能的模式，然后从所有可能的LIKE中找到最大值。不确定SQL现在是否能真正优化它，但未来可能会有所改善。

-- test data

DECLARE @token NVARCHAR(100)
SET @token = 'Blogroller'

DECLARE @titles TABLE (Title NVARCHAR(100))

INSERT @titles VALUES
('Blogroll'),
('Bottom Menu'),
('Business'),
('Entertainment'),
('extend')

-- solution

DECLARE @patterns TABLE (part NVARCHAR(100) PRIMARY KEY, tokenLen int)

WHILE (LEN(@token) > 0) 
BEGIN
    INSERT @patterns VALUES (@token, LEN(@token))
    SET @token = SUBSTRING(@token, 1, LEN(@token) - 1)
END

SELECT MAX(patterns.tokenLen)
FROM @titles titles
INNER JOIN @patterns patterns ON titles.Title LIKE '%' + patterns.part + '%'

- Lanorkin

Answer 3

如果您不介意声明一些变量并运行一个简单的循环，您可以这样做：

declare @phrase as varchar(100) = 'blogroller'
declare @match_length as int = 0

while len(@phrase) > 0
begin
    if (select count(title) from titles where title like '%' + @phrase + '%') > 0
    begin
        set @match_length = len(@phrase)
        break
    end

    set @phrase = LEFT(@phrase, len(@phrase) - 1)
end

print @match_length

- Szymon

---

搜索功能并且有数千个标题的引用，这是一个好选择吗？ - Sandip Bantawa

不一定需要，但如果你只有一个搜索短语，也许不是很糟糕。你的问题并不容易解决，可能很难找到一个非常好的解决方案。 - Szymon

Answer 4

您可以使用一些带有数字的表格（例如示例中使用的master..spt_values）将搜索词分解为所有可能的较短词汇，然后连接它以查找匹配的字符数：

设置：

CREATE TABLE Something (Title NVARCHAR(MAX));
INSERT Something (Title)
VALUES ('Blogroll')
,('Bottom Menu')
,('Business')
,('Entertainment')
,('extend')

查询：

DECLARE @SearchTerm NVARCHAR(MAX);
SET @SearchTerm = 'Blogroller';

WITH CTE_SearchBroken AS 
(
    SELECT LEFT(@SearchTerm, number) BrokenTerm 
    FROM master..spt_values n
    WHERE n.type = 'P' AND n.number <= LEN(@SearchTerm)
)
, CTE_PreliminaryResults AS 
(
    SELECT *, LEN(BrokenTerm) AS BrokenAt 
    FROM Something s 
    LEFT JOIN CTE_SearchBroken b ON s.Title LIKE '%' + b.BrokenTerm + '%'
)
SELECT Title
    , MAX(BrokenAt) AS BrokenAt
    , CASE WHEN LEN(@SearchTerm) = MAX(BrokenAt) THEN 1 ELSE 0 END AS Found
FROM CTE_PreliminaryResults
GROUP BY Title

- Nenad Zivkovic

Answer 5

根据Gordon的回答，您可以编写一个自动循环遍历所有可能性的函数。这与Lanorkin的回答类似；他的性能可能更好，因为它充分利用了内部连接；您可能需要在那里进行一些测试。

CREATE FUNCTION dbo.MatchLen(@needle AS varchar(max), @haystack AS varchar(max)) RETURNS int
AS
BEGIN
    DECLARE @len AS int = LEN(@needle)
    WHILE @len > 0 AND @haystack NOT LIKE '%' + REPLACE(REPLACE(REPLACE(LEFT(@needle, @len), '[', '[[]'), '%', '[%]'), '_', '[_]') + '%'
        SET @len = @len - 1
    RETURN @len
END

例子：

SELECT dbo.MatchLen('blogroller', 'This blog has no blogroll, sir!')

返回8。

- Ruud Helderman

Answer 6

请尝试以下操作：

select 
    *,
    LEN(Title) MatchLength
From YourTable
where 'Blogroller' like Title+'%'

或者

select 
    *,
    LEN(Title) MatchLength
From YourTable
where 'Blogroller' like '%'+Title+'%'

- TechDo

---

不是要点踩，但他不想要LEN，他想找出哪个LEN在模式中可以起作用。 - Lanorkin

@Lanorkin 我接受这一点，但上述查询是否返回了错误的结果？ - TechDo

他想要匹配字符的数量。因此，如果标题是“大”，则有一个匹配字符（第一个字符）。 - Tim Schmelter

但是如果我的“where”中甚至有任何单词包括“roll”怎么办？ - Sandip Bantawa

@TimSchmelter 是的 - 如果你尝试使用'Menu'，但我现在真的很喜欢你的方法，当我开始思考它时。 - Lanorkin