7645 Null or empty full-text predicate

I have a query that ran fine on SQL2005 but moving the database to SQL2008 gives me the error from the title.

The code that is the problem is a call to CONTAINS, CONTAINSTABLE or FREETEXT with an empty parameter. However I’m trying to only call or join when there is a value like such

where (@search_term = '' or (FREETEXT(lst.search_text, @search_term)))

or

left join containstable (listing_search_text, search_text,  @search_term) ftb on l.listing_id = ftb.[key] 
	and len(@search_term) > 0

However I cannot find any workaround for this to work on SQL2008. Any ideas?

I know I can do dynamic SQL or have a if statement with two different cases (select with FT join, select without FT join. Any better workaround which doesn’t require doing this?

Null or empty full-text predicate

I got this error when I supplied null value to @keyword variable.And I found a solution to resolve by setting the @keyword value to ”. The problem is when the value is set to ” and when you run

mysql condition to select empty fulltext cell?

i need write a select query to find the number of rows which have an empty fulltext field but for some reason both: select count(id) from table where field is null; and select count(id) from table wh

sql fulltext returning null

I’m using the joomla CMS to write to my db and have written a custom front end. I’m trying to obtain the contents of the ‘fulltext’ row. I’ve tried mysql_fetch_assoc[‘fulltext’] and mysql_result($sql,

Can fulltext search work if the document type’s value is empty in SqlServer?

I create a fulltext search index on a varbinary column in SqlServer ,and add a type column for it.I want to know,If I leave the type column’s value to be null or empty or .,what will happen when I s

Why is the null predicate called null, not nullp?

Basically, the title says it all: In Common Lisp, why is the null predicate called null. not nullp (to conform to other predicates such as evenp or oddp)? Is there a special reason for this?

Why prefer seq over non-empty as a predicate?

The docstring for empty? says Please use the idiom (seq x) rather than (not (empty? x)). MisterMetaphor points out that using seq as a predicate can make sense when used in if-let: (if-let [s (seq m

Postgres fulltext index

CREATE INDEX message_fulltext_idx ON feedback USING gin(to_tsvector(message)); error: functions in index predicate must be marked IMMUTABLE how to avoid this?

SQL: short-circuiting not working. “Null or empty full-text predicate” after upgrading to SQL Server 2012

I have the following query in SQL Server 2005 which works fine: DECLARE @venuename NVARCHAR(100) DECLARE @town NVARCHAR(100) SET @venuename = NULL — normally these are parameters in the stored proc.

How can I test for an empty Breeze predicate?

I’m using Breeze to filter data requested on the client. My code looks a little like this: Client – Creating Filter Predicate var predicates = []; var criteriaPredicate = null; $.each(selectedFilterCr

Empty ArrayList equals null

Is an empty Arraylist (with nulls as its items) be considered as null? So, essentially would the below statement be true: if (arrayList != null) thanks

Answers

I found the answer to this today when converting my own database from SQL 2005 to SQL 2008.

Pass “” for your search term and change the @search_term = ” test to be @search_term = ‘””‘ SQL server will ignore the double quotes and not throw an error.

For example, the following would actually returns all records in the Users table:

declare  @SearchTerm nvarchar(250)

SET @SearchTerm = '""'

select UserId, U.Description, U.UserName
from dbo.Users U
WHERE ((@SearchTerm = '""') OR CONTAINS( (U.Description, U.UserName), @SearchTerm))

If you are using .Net, you might grab a copy of E. W. Bachtal’s FullTextSearch class. His site is very informative: http://ewbi.blogs.com/develops/

This solution didn’t work for me on SQL 2008. The answer seemed pretty clear and was deemed useful but I would get time outs on a table with 2M records. In fact it locked up a server just running the query in SSMS.

It didn’t seem to like the OR in the where clause but I could run the query fine separating the conditions.

I ended up using a UNION successfully as a workaround.

declare  @SearchTerm nvarchar(250)

SET @SearchTerm = '""'

select UserId, U.Description, U.UserName
from dbo.Users U
WHERE ((@SearchTerm = '""') 

UNION 

select UserId, U.Description, U.UserName
from dbo.Users U
WHERE CONTAINS( (U.Description, U.UserName), @SearchTerm)) 

The problem with FTS and the OR operand was fixed in SP2 CU4. The OR condition should run ok without having to UNION if you are on that level or later. We tried a very recent update of SP2 CU8 and FTS works with OR now. Also, searches such as 3.12 which would fail before now work just fine.