0

I am trying to use FREETEXT. The @name field often has nulls and this is not an error in the program. It just means that other search approaches are being used.

Originally I had

DECLARE @Name varchar(50) = ... -- some value which many be NULL or blank

SELECT ID 
FROM dbo.Data WITH (NOLOCK) 
WHERE  FREETEXT(*, @Name)

I tried

DECLARE @Name varchar(50) = ... -- some value which many be NULL or blank

SELECT ID 
FROM dbo.Data WITH (NOLOCK) 
WHERE @Name IS NOT NULL AND  FREETEXT(*, @Name)

But I still get

Msg 7645, Level 15, State 2, Line 3 Null or empty full-text predicate.

What is a good approach to get around this?

additional note

SELECT ID 
FROM dbo.data WITH (NOLOCK) 
WHERE FREETEXT(*, ' ')

also throws and error

James A Mohler
  • 11,060
  • 15
  • 46
  • 72

2 Answers2

1

Update

try this:

SET @Name = ISNULL(NULLIF(@Name, ''), '<null>');

SELECT ID
FROM dbo.Data
WHERE FREETEXT(*, @Name)

older answer:

One option is to use nullif to convert empty strings to null, followed by isnull to convert nulls to a string with a single space.

SELECT ID
FROM dbo.Data
WHERE FREETEXT(*, isnull(nullif(@Name, ''), ' '))
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
1

Updated statement from Zohar seems to be working..I just added a declare statement below:

declare @Name varchar(20) = ''
set @Name = ISNULL(NULLIF(@Name, ''), ''); select Author_id from Author where freetext(*,@Name)

Vijay
  • 124
  • 5