Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I properly query SystemIndex when there are spaces?

Posted on 2014-10-03
5
Medium Priority
?
217 Views
Last Modified: 2015-07-22
Hi!

I have a CLR function that I reference from SQL to carry out full-text searches. The problem I'm having is that the path where the documents reside contains a space i.e. "C:\My Documents\Information". When I place the call I receive no hits. Yet, if I carry out the sample call with a path that contains no spaces i.e. "C:\MyDocuments\Information" I receive a slew of responses.

My query to SystemIndex is formatted like so:

"SELECT System.FileName, System.ItemPathDisplay, System.DateModified FROM SystemIndex WHERE SCOPE = '" + Scope + "' AND CONTAINS('" + Contains + "')"

Open in new window


Does anybody know what I can do to get around this issue. All the examples I find online refer to a path that have no spaces, but client setups vary across the board and we can't enforce that as a rule.

Please help.
0
Comment
Question by:PlawCoder
  • 3
  • 2
5 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40359606
AND CONTAINS('" + Contains.Replace(" ", "") + "')"
0
 

Author Comment

by:PlawCoder
ID: 40359613
The issue here is the SCOPE portion of the query. The path is the one that contains spaces.
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40359625
The solution is to replace space with an empty string . . . you can do that on the scope instead.
0
 

Author Comment

by:PlawCoder
ID: 40359641
Thanks Kyle.

But I can't effectively replace "C:\My Documents\Information" with "C:\MyDocuments\Information". That would yield an entirely different path, which doesn't even exist in my directory.

Perhaps I'm not understanding your statement.
0
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 2000 total points
ID: 40359662
Try the following:
string.Format("SELECT System.FileName, System.ItemPathDisplay, System.ItemType, System.Size, System.ItemDate, System.DateModified FROM " +
" myserver.systemindex WHERE SCOPE='{1}' AND" + 
" System.FileName LIKE " +  " '%{0}%'"
, searchString, searchLocation);

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Viewers will learn how the fundamental information of how to create a table.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
Suggested Courses

916 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