Solved

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

Posted on 2013-02-07
10
650 Views
Last Modified: 2013-02-15
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
Comment
Question by:fmbExpertsExchange
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 38865610
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38866427
What version of SQL Server are you using?
Why can you not search for "Pack" instead of "(Pack)"?
0
 

Author Comment

by:fmbExpertsExchange
ID: 38866571
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38866584
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38866630
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 38866776
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
 

Author Comment

by:fmbExpertsExchange
ID: 38876636
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38877209
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
 

Author Comment

by:fmbExpertsExchange
ID: 38877394
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 38878588
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now