利用SQL用户自定义函数实现
1、在SQL创建用户自定义函数
创建语句:
CREATE function dbo.GetContent(@s varchar(500),@id int)
returns varchar(2000)
as
begin
declare @re varchar(8000)
declare @tj varchar(100)
set @tj='%'+@s+'%' --设置要查询的关键字
select @re = substring(Content,case
when patindex(@tj,Content)<=30 then 1
else patindex(@tj,Content)-30 end
,100)
from Article
where Content like @tj and Articleid=@id
return @re
end
2、使用方法
SELECT ArticleID, Title, dbo.GetContent('" + Keys + "', ArticleID) AS Content,UpdateTime FROM Article ORDER BY UpdateTime DESC;
如果Content中不包含关键字会返回NULL,用下面的方法,如果返回NULL就显示Content的前100个字符。
SELECT ArticleID, Title, CASE WHEN dbo.GetContent('" + Keys + "', ArticleID) IS NULL THEN substring(Content, 0, 100) ELSE dbo.GetContent('" + Keys + "', ArticleID) END + '... ...' AS Content,UpdateTime FROM Article ORDER BY UpdateTime DESC;
1、在SQL创建用户自定义函数
创建语句:
CREATE function dbo.GetContent(@s varchar(500),@id int)
returns varchar(2000)
as
begin
declare @re varchar(8000)
declare @tj varchar(100)
set @tj='%'+@s+'%' --设置要查询的关键字
select @re = substring(Content,case
when patindex(@tj,Content)<=30 then 1
else patindex(@tj,Content)-30 end
,100)
from Article
where Content like @tj and Articleid=@id
return @re
end
2、使用方法
SELECT ArticleID, Title, dbo.GetContent('" + Keys + "', ArticleID) AS Content,UpdateTime FROM Article ORDER BY UpdateTime DESC;
如果Content中不包含关键字会返回NULL,用下面的方法,如果返回NULL就显示Content的前100个字符。
SELECT ArticleID, Title, CASE WHEN dbo.GetContent('" + Keys + "', ArticleID) IS NULL THEN substring(Content, 0, 100) ELSE dbo.GetContent('" + Keys + "', ArticleID) END + '... ...' AS Content,UpdateTime FROM Article ORDER BY UpdateTime DESC;