Use user defined functions within contains

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

The following line works fine:

SQL

select * from newsdetails
where dbo.udf_StripHTML(NewsDescription) like '%fiber%'

But I want to do something like this:

SQL

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

Posted 16-Sep-15 21:48pm

Member 10441509

Add a Solution

Comments

VC.J 17-Sep-15 4:07am

what error you are getting

Member 10441509 17-Sep-15 4:48am

Incorrect syntax near '('.

[no name] 17-Sep-15 4:10am

On a first look to the Syntax Definition of CONTAINS, the first argument of CONTAINS needs to be a fieldname. So maybe it is possible to include "dbo.udf_StripHTML(NewsDescription)" in the SELECT field list with an alias Name and use it later in "WHERE CONTAINS(...."

Member 10441509 17-Sep-15 4:51am

select top 10 dbo.udf_StripHTML(NewsDescription) as mydesc ,* from newsdetails where contains(mydesc, '"fiber"')

but gives error

Invalid column name 'mydesc'.

[no name] 17-Sep-15 4:56am

So it seems it is not possible...but maybe an SQL prof will give you a solution.

What is the reason to replace the first statement you Show which is working?

Member 10441509 17-Sep-15 5:28am

Table size is 7GB which is heaving more than 450k records.

if run like query it is giving result in 2hrs,So can not use like .

[no name] 17-Sep-15 5:41am

And why do you think CONTAINS will help? I think also with CONTAINS a full table scan will be necessary.

Member 10441509 17-Sep-15 6:58am

Because even without using user function like query takes almost same time but contains took only 2:15 mins

VC.J 17-Sep-15 7:34am

select top 10 dbo.udf_StripHTML(NewsDescription) as mydesc ,* from newsdetails where contains(mydesc, '"fiber"')
this will not work
you can store in a temp table and that use that may work, but in a single line it won't

Member 10441509 17-Sep-15 8:16am

it gives error:-

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view '#tempnews' because it is not full-text indexed.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2015-09-17T04:08:00

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 your CONTAINS query against that column.

SQL

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

VC.J · Answer 2 · 2015-09-17T19:05:00

Solution 2

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[^]

Posted 17-Sep-15 19:05pm

VC.J