Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Full Text Search Wildcards
Add to briefcase

Full Text Search Wildcards Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Doug Andersen
Posted Tuesday, September 23, 2008 11:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 21, 2011 3:43 PM
Points: 13, Visits: 127
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
Jerry Hung
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: Yesterday @ 9:16 AM
Points: 768, Visits: 1,161

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
Doug Andersen
Posted Tuesday, September 23, 2008 1:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 21, 2011 3:43 PM
Points: 13, Visits: 127
Nope, because full-text searches complain when you attempt to search with a null.
Post #574755
Jerry Hung
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: Yesterday @ 9:16 AM
Points: 768, Visits: 1,161
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.