Is Full Text Search CONTAINS clause always evaluated?
up vote
2
down vote
favorite
Is there a way to avoid the error Null or empty full-text predicate in the example below ?
DECLARE @SearchText VARCHAR(1000);
SET @SearchText = ''
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
AND
*/
(
@SearchText = ''
OR
(
@SearchText <> ''
AND
CONTAINS((myField1, myField2), @SearchText)
)
)
I could be doing like this, but I want to avoid duplicating the code :
DECLARE @SearchText VARCHAR(1000);
SET @SearchText = ''
IF @SearchText = ''
BEGIN
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
*/
END
ELSE
BEGIN
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
AND
*/
(
@SearchText = ''
OR
(
@SearchText <> ''
AND
CONTAINS((myField1, myField2), @SearchText)
)
)
END
[EDIT]
I found the answer here
So the solution is to set the @SearchText to '""' instead of leaving it empty.
tsql full-text-search
edited May 23 '17 at 11:56
Community♦
11
asked Feb 9 '12 at 18:29
leoinfo
5,84752944
add a comment |
up vote
2
down vote
favorite
Is there a way to avoid the error Null or empty full-text predicate in the example below ?
DECLARE @SearchText VARCHAR(1000);
SET @SearchText = ''
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
AND
*/
(
@SearchText = ''
OR
(
@SearchText <> ''
AND
CONTAINS((myField1, myField2), @SearchText)
)
)
I could be doing like this, but I want to avoid duplicating the code :
DECLARE @SearchText VARCHAR(1000);
SET @SearchText = ''
IF @SearchText = ''
BEGIN
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
*/
END
ELSE
BEGIN
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
AND
*/
(
@SearchText = ''
OR
(
@SearchText <> ''
AND
CONTAINS((myField1, myField2), @SearchText)
)
)
END
[EDIT]
I found the answer here
So the solution is to set the @SearchText to '""' instead of leaving it empty.
tsql full-text-search
edited May 23 '17 at 11:56
Community♦
11
asked Feb 9 '12 at 18:29
leoinfo
5,84752944
The search for a "match all" pattern led to this.
– HABO
Feb 9 '12 at 19:38
A default pattern like'"a*" or "b*" or ...'matches most words. There is still the opportunity to lose rows that only contain noise words, numbers, ... .
– HABO
Feb 9 '12 at 19:45
@userd* - The idea is that when I have nothing to search for (@SearchText=""), I get an error (Null or empty full-text predicate). I have a lot of other filters there in WHERE clause, so I do not want to duplicate all that logic. I was hoping I can avoid that...
– leoinfo
Feb 9 '12 at 20:14
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
Is there a way to avoid the error Null or empty full-text predicate in the example below ?
DECLARE @SearchText VARCHAR(1000);
SET @SearchText = ''
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
AND
*/
(
@SearchText = ''
OR
(
@SearchText <> ''
AND
CONTAINS((myField1, myField2), @SearchText)
)
)
I could be doing like this, but I want to avoid duplicating the code :
DECLARE @SearchText VARCHAR(1000);
SET @SearchText = ''
IF @SearchText = ''
BEGIN
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
*/
END
ELSE
BEGIN
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
AND
*/
(
@SearchText = ''
OR
(
@SearchText <> ''
AND
CONTAINS((myField1, myField2), @SearchText)
)
)
END
[EDIT]
I found the answer here
So the solution is to set the @SearchText to '""' instead of leaving it empty.
tsql full-text-search
edited May 23 '17 at 11:56
Community♦
11
asked Feb 9 '12 at 18:29
leoinfo
5,84752944
Is there a way to avoid the error Null or empty full-text predicate in the example below ?
DECLARE @SearchText VARCHAR(1000);
SET @SearchText = ''
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
AND
*/
(
@SearchText = ''
OR
(
@SearchText <> ''
AND
CONTAINS((myField1, myField2), @SearchText)
)
)
I could be doing like this, but I want to avoid duplicating the code :
DECLARE @SearchText VARCHAR(1000);
SET @SearchText = ''
IF @SearchText = ''
BEGIN
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
*/
END
ELSE
BEGIN
SELECT * FROM myTable
WHERE
/*
SOME CONTITIONS
AND
*/
(
@SearchText = ''
OR
(
@SearchText <> ''
AND
CONTAINS((myField1, myField2), @SearchText)
)
)
END
[EDIT]
I found the answer here
So the solution is to set the @SearchText to '""' instead of leaving it empty.
tsql full-text-search
tsql full-text-search
edited May 23 '17 at 11:56
Community♦
11
asked Feb 9 '12 at 18:29
leoinfo
5,84752944
edited May 23 '17 at 11:56
Community♦
11
asked Feb 9 '12 at 18:29
leoinfo
5,84752944
edited May 23 '17 at 11:56
Community♦
11
edited May 23 '17 at 11:56
Community♦
11
edited May 23 '17 at 11:56
Community♦
11
11
asked Feb 9 '12 at 18:29
leoinfo
5,84752944
asked Feb 9 '12 at 18:29
leoinfo
5,84752944
asked Feb 9 '12 at 18:29
leoinfo
5,84752944
5,84752944
The search for a "match all" pattern led to this.
– HABO
Feb 9 '12 at 19:38
A default pattern like'"a*" or "b*" or ...'matches most words. There is still the opportunity to lose rows that only contain noise words, numbers, ... .
– HABO
Feb 9 '12 at 19:45
@userd* - The idea is that when I have nothing to search for (@SearchText=""), I get an error (Null or empty full-text predicate). I have a lot of other filters there in WHERE clause, so I do not want to duplicate all that logic. I was hoping I can avoid that...
– leoinfo
Feb 9 '12 at 20:14
add a comment |
The search for a "match all" pattern led to this.
– HABO
Feb 9 '12 at 19:38
A default pattern like'"a*" or "b*" or ...'matches most words. There is still the opportunity to lose rows that only contain noise words, numbers, ... .
– HABO
Feb 9 '12 at 19:45
@userd* - The idea is that when I have nothing to search for (@SearchText=""), I get an error (Null or empty full-text predicate). I have a lot of other filters there in WHERE clause, so I do not want to duplicate all that logic. I was hoping I can avoid that...
– leoinfo
Feb 9 '12 at 20:14
The search for a "match all" pattern led to this.
– HABO
Feb 9 '12 at 19:38
The search for a "match all" pattern led to this.
– HABO
Feb 9 '12 at 19:38
A default pattern like
'"a*" or "b*" or ...' matches most words. There is still the opportunity to lose rows that only contain noise words, numbers, ... .– HABO
Feb 9 '12 at 19:45
A default pattern like
'"a*" or "b*" or ...' matches most words. There is still the opportunity to lose rows that only contain noise words, numbers, ... .– HABO
Feb 9 '12 at 19:45
@userd* - The idea is that when I have nothing to search for (@SearchText=""), I get an error (Null or empty full-text predicate). I have a lot of other filters there in WHERE clause, so I do not want to duplicate all that logic. I was hoping I can avoid that...
– leoinfo
Feb 9 '12 at 20:14
@userd* - The idea is that when I have nothing to search for (@SearchText=""), I get an error (Null or empty full-text predicate). I have a lot of other filters there in WHERE clause, so I do not want to duplicate all that logic. I was hoping I can avoid that...
– leoinfo
Feb 9 '12 at 20:14
add a comment |
1 Answer
1
active
oldest
votes
up vote
3
down vote
accepted
I found the answer here.
The solution is to set the @SearchText to '""' instead of leaving it empty.
edited Nov 10 at 3:53
Pang
6,8201563101
answered Feb 9 '12 at 20:28
leoinfo
5,84752944
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
accepted
I found the answer here.
The solution is to set the @SearchText to '""' instead of leaving it empty.
edited Nov 10 at 3:53
Pang
6,8201563101
answered Feb 9 '12 at 20:28
leoinfo
5,84752944
add a comment |
up vote
3
down vote
accepted
I found the answer here.
The solution is to set the @SearchText to '""' instead of leaving it empty.
edited Nov 10 at 3:53
Pang
6,8201563101
answered Feb 9 '12 at 20:28
leoinfo
5,84752944
add a comment |
up vote
3
down vote
accepted
up vote
3
down vote
accepted
I found the answer here.
The solution is to set the @SearchText to '""' instead of leaving it empty.
edited Nov 10 at 3:53
Pang
6,8201563101
answered Feb 9 '12 at 20:28
leoinfo
5,84752944
I found the answer here.
The solution is to set the @SearchText to '""' instead of leaving it empty.
edited Nov 10 at 3:53
Pang
6,8201563101
answered Feb 9 '12 at 20:28
leoinfo
5,84752944
edited Nov 10 at 3:53
Pang
6,8201563101
edited Nov 10 at 3:53
Pang
6,8201563101
edited Nov 10 at 3:53
Pang
6,8201563101
6,8201563101
answered Feb 9 '12 at 20:28
leoinfo
5,84752944
answered Feb 9 '12 at 20:28
leoinfo
5,84752944
answered Feb 9 '12 at 20:28
leoinfo
5,84752944
5,84752944
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f9216608%2fis-full-text-search-contains-clause-always-evaluated%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
The search for a "match all" pattern led to this.
– HABO
Feb 9 '12 at 19:38
A default pattern like
'"a*" or "b*" or ...'matches most words. There is still the opportunity to lose rows that only contain noise words, numbers, ... .– HABO
Feb 9 '12 at 19:45
@userd* - The idea is that when I have nothing to search for (@SearchText=""), I get an error (Null or empty full-text predicate). I have a lot of other filters there in WHERE clause, so I do not want to duplicate all that logic. I was hoping I can avoid that...
– leoinfo
Feb 9 '12 at 20:14