Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Full Text Search Wildcards Expand / Collapse
Author
Message
Posted Tuesday, September 23, 2008 11:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 26, 2013 11:56 AM
Points: 13, Visits: 130
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,'*')
Post #574669
Posted Tuesday, September 23, 2008 12:40 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:26 AM
Points: 775, Visits: 1,203

select *
from table
where country in (@countryparameter)
and customertype in (@customertypeparameter)
and (@name IS NULL OR contains(name,@name))


would this work?


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #574715
Posted Tuesday, September 23, 2008 1:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 26, 2013 11:56 AM
Points: 13, Visits: 130
Nope, because full-text searches complain when you attempt to search with a null.
Post #574755
Posted Wednesday, September 24, 2008 9:35 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:26 AM
Points: 775, Visits: 1,203
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 :P


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))



SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #575325
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse