September 23, 2008 at 11:38 am
I'm trying to figure out a wildcard string that will bring back essentially all results in full-text search, I'm thinking something like [a-z]*.
Why?
Because it's tied to a reporting services report with several optional parameters, including name. So if the name isn't used as a parameter (it's left null) then I need to substitute a wildcard that won't limit the search in that regard (and just limit on the other parameters).
As in...
select *
from table
where country in (@countryparameter)
and customertype in (@customertypeparameter)
and contains(name,'*')
September 23, 2008 at 12:40 pm
select *
from table
where country in (@countryparameter)
and customertype in (@customertypeparameter)
and (@name IS NULL OR contains(name,@name))
would this work?
September 23, 2008 at 1:45 pm
Nope, because full-text searches complain when you attempt to search with a null.
September 24, 2008 at 9:35 am
Doug Andersen (9/23/2008)
Nope, because full-text searches complain when you attempt to search with a null.
Yes, the stupid NULL predicate
But maybe a clever getaround?
Hopefully the short circuit will pick up the first condition (@name = 'nothing') without calling CONTAINS
You never know with SQL full-text functions 😛
IF @name IS NULL
SET @name = 'nothing'
select *
from table
where country in (@countryparameter)
and customertype in (@customertypeparameter)
and (@name = 'nothing' OR contains(name,@name))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply