多词搜索项拆分匹配存储过程异常问题求助

需求：当搜索词为「Hello Connor Mcgregor」这类多词内容时，需要拆分每个词（示例如下）：

searchTerm[0] = "Hello";
searchTerm[1] = "Conner"
searchTerm[2] = "Mcgregor"

分别匹配数据表tblThread中的title或questionText字段，返回对应的行。

本人此前长期使用EntityFramework，尝试LINQ后选择采用ADO.NET结合存储过程实现该功能。

尝试的第一段代码 #

这段代码未能成功将拆分后的词写入临时表：

DECLARE @pos INT
DECLARE @len INT
DECLARE @value nvarchar(80)
DECLARE @sql nvarchar(max)

set @pos = 0
set @len = 0

WHILE CHARINDEX(' ', @searchTerm, @pos+1) > 0
BEGIN
        set @len = CHARINDEX(',', @searchTerm, @pos+1) - @pos
        set @value = SUBSTRING(@searchTerm, @pos, @len)            

         if exists (select title, questionText from tblThread where title like @value + '%')
         BEGIN
            set @sql = (select title, questionText from tblThread where title like @value + '%')
            Print @sql
            --DO YOUR MAGIC HERE        
         END
        set @pos = CHARINDEX(',', @searchTerm, @pos+@len) +1
END 

修改后的存储过程 #

调整后写出以下存储过程，但执行exec getThreadsBySearchTerm 'Question is'时发现，临时表仅添加了「Question」，未添加「is」：

ALTER PROCEDURE [dbo].[getThreadsBySearchTerm] 

    @searchTerm nvarchar(80)

AS
BEGIN
SET NOCOUNT ON;

    
    DECLARE @searchTermTbl TABLE (searchTerm nvarchar(max))
    DECLARE @value nvarchar(max)
    DECLARE @pos INT
    DECLARE @len INT
    DECLARE @sql nvarchar(max)

    DECLARE @sqlWithSpace Int

    set @pos = 0
    set @len = 0

    

    while CHARINDEX(' ', @searchTerm, @pos+1) > 0
    Begin
            set @len = CHARINDEX(' ', @searchTerm, @pos+1) - @pos
            set @value = SUBSTRING(@searchTerm, @pos, @len)            

            insert into @searchTermTbl values(@value)
            set @pos = CHARINDEX(' ', @searchTerm, @pos+@len) +1

    End 
     

     
    if exists(select * from @searchTermTbl)
        begin
            select title, questionText from tblThread
            inner join @searchTermTbl stt on tblThread.title Like '%' + stt.searchTerm + '%'
            inner join @searchTermTbl sttt on tblThread.questionText Like '%' + sttt.searchTerm + '%'
        end
    else
        begin
            select title, questionText from tblThread
            where title like '%' + @searchTerm + '%'
            or questionText like '%' + @searchTerm + '%'
        end

    select * from @searchTermTbl
END
GO


exec getThreadsBySearchTerm 'Question is'

解决方案 #

问题原因 #

1. SQL的字符串索引从1开始，初始设置@pos = 0会导致SUBSTRING取到错误内容
2. 循环仅处理到倒数第二个词的空格位置，最后一个词因找不到后续空格，不会进入循环，因此没被插入临时表
3. 原查询逻辑用两次内连接，错误要求行的title包含某个词且questionText包含某个词，不符合“每个词匹配title或questionText任意一个”的需求

修正后的存储过程 #

ALTER PROCEDURE [dbo].[getThreadsBySearchTerm] 
    @searchTerm nvarchar(80)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @searchTermTbl TABLE (searchTerm nvarchar(max))
DECLARE @value nvarchar(max)
DECLARE @pos INT
DECLARE @len INT

-- 先去除搜索词首尾空格，避免生成空字符串
SET @searchTerm = LTRIM(RTRIM(@searchTerm))
SET @pos = 1

-- 处理中间带空格的词
WHILE CHARINDEX(' ', @searchTerm, @pos) > 0
BEGIN
    SET @len = CHARINDEX(' ', @searchTerm, @pos) - @pos
    SET @value = SUBSTRING(@searchTerm, @pos, @len)
    -- 仅插入非空的词
    IF @value <> ''
        INSERT INTO @searchTermTbl VALUES(@value)
    -- 移动指针到下一个词的起始位置
    SET @pos = CHARINDEX(' ', @searchTerm, @pos) + 1
END

-- 插入最后一个词（循环结束后剩下的部分）
SET @value = SUBSTRING(@searchTerm, @pos, LEN(@searchTerm) - @pos + 1)
IF @value <> ''
    INSERT INTO @searchTermTbl VALUES(@value)

-- 修正查询逻辑：只要行的title或questionText包含任意一个拆分后的词，就返回该行
IF EXISTS(SELECT * FROM @searchTermTbl)
BEGIN
    SELECT DISTINCT t.title, t.questionText 
    FROM tblThread t
    WHERE EXISTS(
        SELECT 1 FROM @searchTermTbl stt
        WHERE t.title LIKE '%' + stt.searchTerm + '%' 
           OR t.questionText LIKE '%' + stt.searchTerm + '%'
    )
END
ELSE
BEGIN
    -- 无拆分词时，直接匹配整个搜索词
    SELECT title, questionText 
    FROM tblThread
    WHERE title LIKE '%' + @searchTerm + '%'
       OR questionText LIKE '%' + @searchTerm + '%'
END

-- 可选：查看拆分后的词
SELECT * FROM @searchTermTbl
END
GO

关键修改点 #

• 去除首尾空格，避免处理无效的空字符串
• 调整@pos初始值为1，符合SQL字符串索引规则
• 循环结束后单独插入最后一个词，确保所有词都被处理
• 用EXISTS子句实现“任意词匹配title或questionText”的逻辑，同时用DISTINCT避免重复返回同一行
• 增加非空判断，防止插入空词到临时表

内容的提问来源于stack exchange，提问作者redoc01