9

I'm using SQL Server 2014's full-text search capabilites to find documents in a database that start with a given prefix. Some queries, however, do not yield any results, while they should.

Take the following example:

SELECT * FROM [Profile].[DocumentView] WHERE CONTAINS(Content, '"Friedenseins*"')

(24 row(s) affected)

SELECT * FROM [Profile].[DocumentView] WHERE CONTAINS(Content, '"Friedensein*"')

(0 row(s) affected)

SELECT * FROM [Profile].[DocumentView] WHERE CONTAINS(Content, '"Friedensei*"')

(29 row(s) affected)

I understand the first and third result, but not the second one. The stoplist for the full-text index is switched off. The language for the wordbreaker is set to German.

EDIT:
The suggestion to use FREETEXT instead is not a solution for this particular case, as I need CONTAINS's proximity search feature.

Mathias Becher
  • 703
  • 8
  • 20
  • Means you don't have "word" in Content column with the following ("Friedensein"), but you have "words" in the column with the following startings("Friedenseins","Friedensei"). – knkarthick24 Nov 18 '14 at 15:25
  • The target word is actually "Friedenseinsätze", which is found by the fist and the third query, but not by the second. – Mathias Becher Nov 18 '14 at 15:26
  • Never come across this kind of situation, eager to hear from experts. +1 – knkarthick24 Nov 18 '14 at 15:32
  • 1
    I have put a [sample DDL and reproduction of the problem on SQL Fiddle](http://sqlfiddle.com/#!6/954da/1) – GarethD Nov 18 '14 at 16:51
  • 1
    I created an issue on Connect: https://connect.microsoft.com/SQLServer/feedback/details/1032815 – Mathias Becher Nov 18 '14 at 17:24
  • Strange indeed. Change that first letter from F to G and suddenly it works. I played with the sqlfiddle and found the same problem using English instead of German. I've worked pretty extensively with SQL Server full text and never seen anything like this! – Keith Dec 02 '14 at 22:19
  • See the link to connect for an anwser by Microsoft. – Mathias Becher Dec 16 '14 at 16:48
  • Please consider posting a self answer as you have an explanation now. – Martin Smith Dec 27 '14 at 21:23

2 Answers2

2

Thank you for posting this question Mathias. I just wanted to point out the suggestion from Microsoft (via https://connect.microsoft.com/SQLServer/feedback/details/1032815) was to switch to the format

 SELECT * FROM [Profile].[DocumentView] WHERE FREETEXT(Content, '"Friedensein*"')

While this doesn't help Mathias, it was able to help me out and might help the reader.

In my case, I had a problem with searching a date string stored in a text field (nvarchar). My simple example was that this DIDN'T WORK: 

select * from Sample
where CONTAINS(*, '"1/5/2015*"')

... returns nothing. However, when I switch to FREETEXT as follows, it does return as expected.

select * from Sample
where FREETEXT(*, '"1/5/2015*"')

I am also using SQL 2014. Please vote up the MS Connect link if you are also having this problem.

UPDATE 1/8/2015:

I have since discovered that this solution does not work in my case since FREETEXT brings back other date values that are "similar" or so it seems. With my example of 1/5/2015 above, I'm also getting back hits on 1/2/2015, 1/6/2015 and so on. In fact, if I query against 12/5/2014 (former year) I get hits all across the year, so the "similar" seems to be defined by the year in this case.

For reference, here is the SQL documentation: http://msdn.microsoft.com/en-us/library/ms176078.aspx

ebol2000
  • 1,195
  • 11
  • 16
1

The problem is with the word-breakers, which do not include the phrase id, so the second query gets translated into the equivalent of (pseudo-SQL)

SELECT * FROM [Profile].[DocumentView] WHERE Content LIKE
    ((friedensein* OR frieden*) in Position 1) AND (sein* in Position 2)

But "Friedenseinsätze" has "einsätze" in Position 2. With the proper phrase id, the query would become

SELECT * FROM [Profile].[DocumentView] WHERE Content LIKE
    ((friedensein* in Position 1) OR ((frieden* in Position 1) AND (sein* in Position 2)))

The Office team has been informed. The Connect Thread will be updated when new information is available.

UPDATE: The issue was resolved as Won't Fix.

Mathias Becher
  • 703
  • 8
  • 20