• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 896
  • 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 ShiloCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
PortletPaulCommented:
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
 
PortletPaulCommented:
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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