SQL 根据中文分词组件来搜索表

 

　　网上有很多的关于中文分词组件实现分词功能的例子，如输入中秋送礼会自动分词变成中秋/送礼，博客园也会自动分割成博客/园。

　　

　　得到这些分词结果再写一段存储过程下一步搜索，如下：

　　

　　

CREATEPROCEDURE [dbo].[Search_Test]
    @Keyword                nvarchar(50)=''
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @Condition NVARCHAR(1000)
        SET @Condition = '1 = 1'
    DECLARE @Tables VARCHAR(100)
    SET @Tables = '[vw_tmp] a '

    DECLARE @Sql NVARCHAR(4000)
        SET @Sql = ''

    IF @Keyword <>''
    BEGIN
        declare @Filter as varchar(max)
        declare @Str as varchar(1000)
        if right(@Keyword,1)<>'/'
            set @Str=@Keyword+'/'
        else
            set @Str=@Keyword
        set @Filter=' '
        declare   @Ch   as   varchar(100)   
           
        while(@Str<>'')   
        begin   

            set   @Ch=left(@Str,charindex('/',@Str,1)-1)   
            set   @Str=stuff(@Str,1,charindex('/',@Str,1),'')   
            set @Filter=@Filter+' or [product_tag] LIKE ''%' + @Ch + '%'''
        end

        if charindex('/',@Keyword,1)>0
            set @Keyword=replace(@Keyword,'/','')
        SET @Condition = '  (a.[product_tag]  LIKE ''%' + @Keyword + '%'' '+ @Filter+' )'

    END

    SET @Sql = @Sql + '
        SELECT
        top 1 a.*
        FROM
            ' + @Tables + '
        WHERE
            ' + @Condition +'order by CHARINDEX('''+@Keyword+''', product_tag, 1) desc' --排序根据最匹配的内容放在最前面

    print @sql 

    EXEC sp_executesql @Sql,
        N'@keyword    nvarchar(500)=NULL',
        @Keyword
END

 

 

 

EXEC    @return_value = [dbo].[Search_Test]
        @keyword = N'中秋/春节'

SELECT    'Return Value' = @return_value

 

 

 

　　返回的SQL为

 

        SELECT
  top 1 a.*
        FROM
            [vw_tmp] a
        WHERE
              (a.[product_tag]  LIKE '%中秋春节%'   or [product_tag] LIKE '%中秋%' or [product_tag] LIKE '%春节%' ) order by CHARINDEX('中秋春节', product_tag, 1) desc