0

I'm trying to replace a Keyword Analyser based Lucene.NET index with an SQL Server 2008 R2 based one.

I have a table that contains custom indexed fields that I need to query upon. The value of the index column (see below) is a combination of name/ value pairs of the custom index fields from a series of .NET types - the actual values are pulled from attributes at run time, because the structure is unknown.

I need to be able to search for set name and value pairs, using ANDs and ORs and return the rows where the query matches.

Id          Index
====================================================================
1           [Descriptor.Type]=[5][Descriptor.Url]=[/]
2           [Descriptor.Type]=[23][Descriptor.Url]=[/test]
3           [Descriptor.Type]=[25][Descriptor.Alternative]=[hello]
4           [Descriptor.Type]=[26][Descriptor.Alternative]=[hello][Descriptor.FriendlyName]=[this is a test]

A simple query look like this:

select * from Indices where contains ([Index], '[Descriptor.Url]=[/]');

That query will results in the following error:

Msg 7630, Level 15, State 2, Line 1
Syntax error near '[' in the full-text search condition '[Descriptor.Url]=[/]'.

So with that in mind, I altered the data in the Index column to use | instead of [ and ]:

select * from Indices where contains ([Index], '|Descriptor.Url|=|/|');

Now, while that query is now valid, when I run it all rows containing Descriptor.Url and starting with / are returned, instead of the records (exactly one in this case) that exactly matches.

My question is, how can I escape the query to account for the [ and ] and ensure that just the exact matching row is returned?

A more complex query looks a little like this:

select * from Indices where contains ([Index], '[Descriptor.Type]=[12] AND ([Descriptor.Url]=[/] OR [Descriptor.Url]=[/test])');

Thanks,
Kieron

Kieron
  • 26,748
  • 16
  • 78
  • 122

2 Answers2

1

Your main issue is in using a SQL wordbreaker, and the CONTAINS syntax. By default, SQL wordbreakers eliminates punctuation, and normalizes numbers, dates, urls, email addresses, and the like. It also lowercases everything, and stems words.

So, for your input string:

[Descriptor.Type]=[5][Descriptor.Url]=[/]

You would have the following tokens added to the index (along with their positions)

descriptor type nn5 5 descriptor url

(Note: the nn5 is a way to simplify quering numbers and dates given in different formats, the original number is also indexed at the same position)

So, as you can see, the punctutation is not even stored in the full text index, and thus, there is no way to query it using the CONTAINS statement.

So your statement:

select * from Indices where contains ([Index], '|Descriptor.Url|=|/|');

Would actually be normalized down to "descriptor url" by the query generator before submitting it to the full text index, thus the hits on all the entries that have "descriptor" next to "url", excluding punctuation.

What you need is the LIKE statement.

GalacticJello
  • 11,235
  • 2
  • 25
  • 35
0

Using "|" as your delimiter causes the contains query to think of OR. Which is why you are getting unexpected results. You should be able to escape the bracket like so:

SELECT * FROM Indices WHERE 
    contains ([Index], '[[]Descriptor.Type]=[[]12]')
Prescott
  • 7,312
  • 5
  • 49
  • 70
  • I have tried that, but my biggest concern is the incorrect results being returned. Thanks. – Kieron Feb 09 '11 at 20:13
  • What error does `select * from Indices where contains ([Index], '[[]Descriptor.Url]=[[]/]');` give you? That *should* allow your query to run correctly on the data before you modified it with pipes. Once you changed your data to pipes, you now have to escape the |. The query should look something like: `select * from Indices where contains ([Index], 'Descriptor.Url\|=\|/\| ESCAPE '\');` - not sure escape works in contains and I don't have a db to test against at the moment – Prescott Feb 09 '11 at 21:16
  • The problem isn't so much the escaping, I can change the delimiters if need be, the main issue is the results returned are not as expected. – Kieron Feb 10 '11 at 09:03
  • So, using brackets `[]` and the escape sequence above, you still get incorrect results? Are they the same incorrect results if you use a pipe? Or a different set? From your description it looks like you couldn't get past the errors in the bracket query. – Prescott Feb 10 '11 at 09:27
  • I was, given the escaping, getting the same incorrect results both ways. – Kieron Feb 10 '11 at 16:14
  • hmm, what if you do `SELECT * FROM Indices WHERE contains ([Index], ' "[[]Descriptor.Type]=[[]12]" ') ` alternatively try it without the extra escaping `[]`. It's been a while since I've had to play with full text queries - a little bit of trial and error here.. – Prescott Feb 10 '11 at 22:13
  • Any punctuation (other than reserved boolean indicators) are removed by the query processor before it is passed to the full text engine. So your query above "[[]Descriptor.Type]=[[]12]" would be reduced to "descriptor type 12" when it is passed to the full text engine. – GalacticJello Feb 16 '11 at 17:15