• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1038
  • Last Modified:

How to search for words inside parenthesis in SQL Server Full Text Search

Hi,

Any one out there has an idea on how to search for words contained inside parenthesis using  SQL Server's Full Text Search.

Sample:
ItemDescription
=============
Magnetic Poster  32"x 60"
Noodles Crayons 2-pack
Menu Coupons with Stamp (Pack of 1000)
Offer - Free Bowl Cards - CORP (Pack of 25)
...

I'm trying to look for (this is the criteria a user types in a textbox):
(Pack)

The records that must be return should be:
Menu Coupons with Stamp (Pack of 1000)
Offer - Free Bowl Cards - CORP (Pack of 25)

Can this be possible using SQL Server Full Text Search?

Thanks in advance,

fmbExpertsExchange.
0
fmbExpertsExchange
Asked:
fmbExpertsExchange
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Aaron ShiloChief Database ArchitectCommented:
hi

i know of no specific way but you could try the NEAR clause

select * from myTable
WHERE CONTAINS (myColumn, 'the NEAR good NEAR person')
0
 
Anthony PerkinsCommented:
What version of SQL Server are you using?
Why can you not search for "Pack" instead of "(Pack)"?
0
 
fmbExpertsExchangeAuthor Commented:
acperkins,

Thanks for your help. The issue is that the end-user is expecting to use:
1. the left parenthesis: as in (pack or meaning starts with a parenthesis followed by the word pack.
2. both parenthesis: (Pack) to mean the word pack inside parenthesis
3. the right parenthesis: as in Pack) or meaning ends with pack and then a parenthesis.

Does this make sense? Could it still be possible?

Thanks again,
fmbExpertsExchange
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
PortletPaulfreelancerCommented:
might need to know what data type ItemDescription is, but try these:
select
*
from your_table
where ItemDescription like '%(Pack%)%'

Open in new window

(worked for me: sql 2008, so does the one below using [ and ] to 'escape' the parentheses)
select
*
from niku.CMN_CAPTIONS_NLS
where name like '%[(]agg%[)]%'

Open in new window

see "Using Wildcard Characters As Literals" at http://msdn.microsoft.com/en-us/library/ms179859.aspx

also: a discussion escaping character(s)
0
 
Anthony PerkinsCommented:
Does this make sense? Could it still be possible?
1. You do that with a prefix search as in CONTAINS(YourColumn, '"pack*"')
2. Try using CONTAINS(YourColumn, '"pack"'), but you may have to change the word-breakers.
3. That is not possible.  There is no leading wildcard with Full-Text Search.
0
 
PortletPaulfreelancerCommented:
ah, I see "full text search" (note to self: it pays to fully read the question...)

The basic issue is that "special characters" are ignored in the full text index - they simply don't exist in that index. So there are no parentheses to find.

Supported Forms of Query Terms (Full-Text Search)
(are there) escape characters in full text search? ans=no

How to correctly parse quotation marks in Full Text Search queries
0
 
fmbExpertsExchangeAuthor Commented:
Thanks all for your responses.

PortlerPaul i have the following question:
Is there somewhere available the list of characters that SQL Server strips (other than the noisewords file available)? For example: Whenever I use #,@ or % characters nothing gets returned. What other characters should I not use in order to make a search?

On the other hand, I tried using the LIKE operator and in that case it returns results. Now my question is then how does all those websites work when they let the user search for any word he/she expects to find? Do these websites use a combination of FullTextSearch and the LIKE operator for returning their results? Does it mean that FullTextSearch can not stand on its own? I'm trying to figure out how to make the end-users life easier :-). If you have any advice on this, I'll be glad to hear from you.

Thanks again,

fmbExpertsExchange.
0
 
Anthony PerkinsCommented:
Is there somewhere available the list of characters that SQL Server strips
And again (http:#a38866427) what version of SQL Server are you using?
0
 
fmbExpertsExchangeAuthor Commented:
I'm using SQL Server:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
      Feb  9 2007 22:47:07
      Copyright (c) 1988-2005 Microsoft Corporation
      Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
0
 
Anthony PerkinsCommented:
What other characters should I not use in order to make a search?
The Word Breaker list largely depends on your language and the version of SQL Server (as it changed a lot in SQL Server 2008).

One way to list all the word breakers is shown here (make sure to change the language as appropriate):
http://dba.stackexchange.com/questions/25823/what-characters-are-word-breakers-in-english-for-sql-server-2005-and-2008-r2
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now