Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-02-07
10
Medium Priority
?
862 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
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.

 
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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

610 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