SQL 根据中文分词组件来搜索表
网上有很多的关于中文分词组件实现分词功能的例子，如输入中秋送礼会自动分词变成中秋/送礼，博客园也会自动分割成博客/园。
代码
得到这些分词结果再写一段存储过程下一步搜索，如下：
代码
<!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->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
@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
<!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->EXEC @return_value = [dbo].[Search_Test]
@keyword = N'中秋/春节'
SELECT 'Return Value' = @return_value
@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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16436858/viewspace-674468/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
