Solved

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

Posted on 2013-02-07
10
768 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 49

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
 
LVL 49

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

719 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