Solved

Full text search does not return results for search query with forward slash

Posted on 2010-11-18
12
2,088 Views
Last Modified: 2012-05-10
Hi,

I have a full text CONTAINS query on a nvarchar column and it does not return any result when the search criteria contains a forward slash "/".

SELECT * FROM Product WHERE CONTAINS (Description, '5/8')
The description I'm expecting to match is 'Adapter 5/8 inch'

To ignore stop words, I have run the below code (code details in the code section below) on the database. This worked for ignoring stop words that were numbers.

Database Configuration:
Microsoft SQL Server Standard Edition (64-bit) SP1
Version: 2008 R2, (10.0.2531.0)
on Windows Server 2008 - Standard - SP2.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'transform noise words', 1
RECONFIGURE
GO

Open in new window

0
Comment
Question by:amoses
[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
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 14

Expert Comment

by:leoahmad
ID: 34163631


SELECT * FROM Product WHERE CONTAINS (Description, '5 + CHAR(47) + 8')


SELECT * FROM Product WHERE Description like '5/8'
0
 

Author Comment

by:amoses
ID: 34163660
The Char(47) option gives an error
SQL Server Database Error: Syntax error near '+' in the full-text search condition '5 + CHAR(47) + 8'.

I need to use full text as this is part of a larger search operation that is simplified in this post.
0
 
LVL 35

Expert Comment

by:James0628
ID: 34178856
While I doubt that it's going to help, leoahmad's first suggestion should look like this:

SELECT * FROM Product WHERE CONTAINS (Description, '5' + CHAR(47) + '8')

 There should be quotes around the 5 and 8.


 I wonder if the / is considered punctuation?  You could try "5/8" or '"5/8"' (single and double quotes) or "5 8" or '"5 8"'.

 James
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 

Author Comment

by:amoses
ID: 34186288
While the concatenation and double quote only options threw errors, the single quote suggestions executed successfully but did not return any results.

Errors:
1. For Concatenation SQL: SQL Server Database Error: Incorrect syntax near '+'.
2. For double quote only option ("5/8" or "5 8"): SQL Server Database Error: Incorrect syntax near '5/8'.

If / is considered a punctuation or some escape character, will it be even indexed by full text index?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34191590
You might try preceding the "/" with a "\" character.  "/" is probably considered to be an escape character, so, in order to look for "/", you have to look for '\/' (not that is a \ folowed by a / ;-).

Alternatively, you could search for '5_8', which would return '578' as well as '5/8'.

Finally, you could try the attached code:
SELECT *
FROM   Product
WHERE  Description LIKE '%5!/8%' ESCAPE '!';

Open in new window

0
 
LVL 35

Expert Comment

by:James0628
ID: 34194415
It's a bit hard to believe that CONTAINS can't handle a simple search like that, but I really don't know what else to suggest.  I don't think I've got any tables here that use full text indexing, so I can't run any tests.  I'm sure that LIKE could do it, as has been suggested, but you've said that you don't want to (or can't) use LIKE.  If there are no other suggestions in the next day or two, you could try the "Request Attention" link, but I don't know if that's likely to help, since they've already sent out one call for help.

 Wish I could be of more help.

 James
0
 

Author Comment

by:amoses
ID: 34196505
Yes James, I solution I need is for a full text search. I tried Diver's suggestions and they work for a LIKE operation but not for CONTAINS.

I get results for a LIKE '%5/8%' but not for CONTAINS(Description, '"5/8"')
0
 

Author Comment

by:amoses
ID: 34196726
Update: I ran a query for
SELECT * FROM Product WHERE CONTAINS (Description, '"*5/8*"')
and it returned results where 5 and 8 occurred in the description even thought "/" did not exist in the descriptions returned.

I also did a test for "String1/String2" and it worked as expected. The problem occurs only for numeric values.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34196951
Did you try the following?
SELECT *
FROM   Product
WHERE  CONTAINTS (Description, '5!/8') ESCAPE '!';

Open in new window

0
 

Author Comment

by:amoses
ID: 34196971
Yes, I get the below error for this query.
SQL Server Database Error: Incorrect syntax near the keyword 'ESCAPE'.
0
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 34203036
I've been doing some looking around.  Apparently the "/" _is_ seen as punctuation, and there doesn't seem to be any way to change that.  You can find a bit more info here:

http://www.developmentnow.com/g/104_2004_11_0_0_400026/Search-string-including-a-forward-slash-doesnt-work-.htm

 It's an old discussion, but it seems to apply.  Somewhat interestingly, it's exactly 6 years old, as of today.

 One suggestion that I've seen for this kind of thing is to actually change your data, replacing the problem character with some other character that is not seen as punctuation (although I have yet to see a list of which "special" characters are considered to be punctuation and which are not), or maybe with a word (eg. 'Adapter 5/8 inch' becomes 'Adapter 5SLASH8 inch').  Then, of course, you'd have to make the same change to your search string.  I suppose whether or not that's practical/possible will depend on your data and search strings.

 Another possibility might be to use a search string like '"*5/8*"', which you said returns results that contain 5 and 8, and combine that with LIKE to narrow down the results.  Performance-wise, that seems like it might be OK, if CONTAINS quickly finds the rows that contain 5 and 8, and LIKE just has to pick the exact matches from those.  But assuming that the search string can vary, trying to build the LIKE string could be a problem.

 Hopefully someone will come up with a better solution, but based on what I've read so far, it doesn't seem too promising.

 James
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

690 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