网上有很多的关于中文分词组件实现分词功能的例子,如输入
中秋送礼会自动分词变成
中秋/
送礼,
博客园也会自动分割成
博客/
园。
得到这些分词结果再写一段存储过程下一步搜索,如下:
代码
<!--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博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-674468/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16436858/viewspace-674468/