asked on

How can I properly query SystemIndex when there are spaces?

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.

Kyle Abrahams, PMP

AND CONTAINS('" + Contains.Replace(" ", "") + "')"

PlawCoder

ASKER

The issue here is the SCOPE portion of the query. The path is the one that contains spaces.

Kyle Abrahams, PMP

The solution is to replace space with an empty string . . . you can do that on the scope instead.

PlawCoder

ASKER

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.

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial