在contains中使用用户定义的函数 [英] Use user defined functions within contains
问题描述
以下行正常工作:
选择 * < span class =code-keyword> from newsdetails
其中 dbo.udf_StripHTML(NewsDescription)喜欢 ' %fiber%'
但是我想做这样的事情:
选择 * 来自 newsdetails
其中 包含(dbo.udf_StripHTML(NewsDescription),' 光纤')
我的功能:
CREATE FUNCTION [dbo]。[udf_StripHTML](@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('<',@ HTMLText)
SET @End = CHARINDEX('>',@ HTMLText,CHARINDEX('<',@ HTMLText))
SET @Length =(@ End - @Start)+ 1
WHILE @Start> 0和@End> 0和@Length> 0
BEGIN
SET @HTMLText = STUFF(@ HTMLText,@ Start,@ Length,'')
SET @Start = CHARINDEX( '<',@ HTMLText)
SET @End = CHARINDEX('>',@ HTMLText,CHARINDEX('<',@ HTMLText))
SET @Length =(@End - @Start)+ 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END
GO
全文索引基于表格的内容。从UDF返回的值不是表格内容的一部分,不能用于全文查询。
在您的上创建一个持久计算列表,并将该列添加到您的全文索引。然后对该列运行CONTAINS
查询。
ALTER 表 newsdetails
ADD NewsDescriptionText As dbo.udf_StripHTML(NewsDescription)PERSISTED
GO
ALTER FULLTEXT INDEX ON newsdetails
ADD (NewsDescriptionText)
GO
SELECT *
FROM newsdetails
WHERE CONTAINS (NewsDescriptionText,' fiber')
GO 跨度>
吨这个链接可能会有所帮助
http://stackoverflow.com/questions/6003240/cannot-use-a-contains-or-freetext-predicate-on-table-or-indexed-view-because-it [ ^ ]
The following line works fine:
select * from newsdetails
where dbo.udf_StripHTML(NewsDescription) like '%fiber%'
But I want to do something like this:
select * from newsdetails
where contains(dbo.udf_StripHTML(NewsDescription), '"fiber"')
My function:
CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
WHILE @Start > 0 AND @End > 0 AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END
GO
A full-text index is based on the contents of your table. The value returned from your UDF is not part of the contents of your table, and cannot be used in a full-text query.
Create a persisted computed column on your table, and add that column to your full-text index. Then run yourCONTAINS
query against that column.
ALTER TABLE newsdetails ADD NewsDescriptionText As dbo.udf_StripHTML(NewsDescription) PERSISTED GO ALTER FULLTEXT INDEX ON newsdetails ADD (NewsDescriptionText) GO SELECT * FROM newsdetails WHERE CONTAINS(NewsDescriptionText, '"fiber"') GO
try this link this might help
http://stackoverflow.com/questions/6003240/cannot-use-a-contains-or-freetext-predicate-on-table-or-indexed-view-because-it[^]
这篇关于在contains中使用用户定义的函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!