Full Text Search 'Near'

using SQL server 2005 I have a table which has full text search index enabled

when I run a search using the 'Near' option it returns rows which meet the criteria, how ever the criteria are not 'near' each other they are several hundred words apart.

What is the problem

How near is near

thanks in advance

Glyn

Hi,

The NEAR operator is used to find words which are in close proximity to one another. For example, consider the following three rows in a table:

TextValue

==========

The product being used is SQL Server 2005 Enterprise Edition

We are using full text search in SQL Server 2005 Enterprise Edition

We are performing a test with full text search in SQL Server 2005 Enterprise Edition

Now if I have a query of the following type:

select TextValue

from dbo.sample_tbl

where contains (TextValue, 'search NEAR Server')

This will return the 2nd and the 3rd rows as both the words are approximately close to each other. The NEAR operator returns those rows which have both the words approximately close to each other. When several proximity terms are chained, all proximity terms must be near each other.

|||

Thanks but the search I have is not working

I have a text string

'FTS_STRING_SAMPLE1 can control instantiation to suit the system environment. Relying on delayed instantiation minimizes the amount of memory required by the application, although it might trigger many server requests when properties are referenced.

Instance classes, objects that represent real database objects, can exist in three levels of instantiation. These are minimal-instantiated (only the minimal required properties are read in one block), partially instantiated (all the properties that use a relatively large amount of memory are read in one block), and fully instantiated. Un-instantiated and fully instantiated are the traditional states of instantiation. The partially instantiated state increases efficiency because a partially instantiated object does not contain values for the full set of object properties. Partial instantiation is the default state for an object that is not directly referenced. When one of these properties is referenced, a fault is generated that prompts a full instantiation of the object FTS_STRING_SAMPLE2.'

SELECT *
FROM FTS_TEST_TABLE
WHERE CONTAINS(FTS_TEST_TEXT, 'FTS_STRING_SAMPLE1 NEAR FTS_STRING_SAMPLE2');

It returns the row, however we believe it shouldn't

Glyn

Full text search help

Hi I have a full text index on my product table.

When I do a search for Record, it returns all values for Record and Records.

Now If I do a search with a spelling mistake say Recod . it doen't return anything.

How can I get the full text to return my query even if there is a spelling mistake ?

Thanks

My query:

SELECT * From Product
WHERE FREETEXT (description, @.SearchString)

Try using Soundex|||

Hi dear,

You have to use LIKE operator

http://www.devguru.com/technologies/t-sql/7116.asp
http://doc.ddart.net/mssql/sql70/la-lz_2.htm

i hope these links will help you in this regard.

Thank You

Regards,

Muhammad Akhtar Shiekh

|||

Thank you both for your replied.

The thing is you cannot use LIKE or SOUNDEX in a FULL TEXT SEARCH.

|||

Hi Sleb ,

According to your question, If you want to get the full text to return your query even if there's a spelling mistake, you may achieve that through some special algorithm.

1. You may extend your keyword into several combinations.

eg:

bus -> bus array[0]
bsu array[1]
ubs array[2]
usb array[3]
sub array[4]
sbu array[5]

2. Make the select statement

string sqltxt="select * from Product where ";

for(int i=0;i<array.length;i++)
{
if( i==0 )
{
sqltxt+=" description like '% " + array[i] + " %'" ;
}
else
{
sqltxt+=" or description like '% " + array[i] + " %'" ;
}
}

In this way, you could make every combination from the original keyword match the content.

3. Make your intelligent spelling correcting more smart.

By now, you could get every combination from the original keyword, but you are not sure which one is the correct spelling. So we advice that you should build up a data table to record the user's searching behavior. eg:

keyword SubmitTimes
======================
bus 231254
bsu 12
sub 265421
sbu 125

If the user submit the keyword as "bsu", we are easy to know the possible keyword which the user wants is "bus" or "sub" but not "sbu" according to the submit times from other users.

If this does not help you, pls feel free to mark the post as Not Answered and reply. Thanks.

|||

Hi

From what I can see there is 2 methods. Method one is to use software that is available on the web to do spell checks, or you can write your own code to do a spell check. There are many text file dictionaries on the web so you do not have to be concerned about that.

I had a similar problem, but have come to an understanding that if a surfer cannot spell when surfing a website or the web then they should not be using the web. You cannot cater for every possible problem that a user can create.

I use the FREETEXT operator. What I have noticed is that it will find a plural of a word if the search is singular. For example search for "Card" will return card and cards.

I am sure by this example I think that MSSQL can be used to do a spell check. However I am not sure.

I hope this information will help you

Regard

Full Text Search for Find Methods

Hello,
I have to implement a method FindProduct, which takes a parameter search query and returns product based on the query.

I think i can use full text search here but don't know how. I tried to read a various way but could not figure out.

123 [DataObjectMethodAttribute(DataObjectMethodType.Select,true)]4public DataLayer.shopDBDataSet.ProductsDataTable GetProducts()5 {6return Adapter.GetProducts();7 }89 [DataObjectMethodAttribute(DataObjectMethodType.Insert,true)]10public bool AddProduct(string UPCCode,string Description,string Size)11 {12 shopDBDataSet.ProductsDataTable products =new shopDBDataSet.ProductsDataTable();13 shopDBDataSet.ProductsRow productRow = products.NewProductsRow();1415 productRow.UPCCode = UPCCode;16 productRow.Description = Description;17 productRow.Size = Size;1819 products.AddProductsRow(productRow);20int rowsAffected = Adapter.Update(products);21return rowsAffected == 1;22 }2324 [DataObjectMethodAttribute(DataObjectMethodType.Delete,true)]25public bool DeleteProduct(string UPCCode)26 {27int rowsAffected = Adapter.Delete(UPCCode);28return rowsAffected == 1;29 }3031 [DataObjectMethodAttribute(DataObjectMethodType.Select,false)]32public DataLayer.shopDBDataSet.ProductsDataTable FindProducts(string searchQuery)33 {3435throw new System.NotImplementedException();3637 }38

You can see the method at line number 32. I could not figure out the way to implement it although i know full text search is a good choice.

I have following table adapter

GetProducts(); GetProductsByManufacturerName(); GetProductsByManufacturerID(), and GetProductsByUPCCode();

Product table contains UPCCode, Description, Size columns

But i need to get the product which returns the description containing search query.

I know some of you have done something similar to this.

Could you please guide me...

Thanks in advance

...

The Full Text index require four predicates CONTAINS,CONTAINSTABLE,FREETEXT and FREETEXTTABLE and the Microsoft search service and the catalog must be populated before you can get search results. You can implement it in your application but there is limited support for SQL Server Express. The links below will take you in the right direction if you decide to implement it. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms142559.aspx
http://msdn2.microsoft.com/en-us/library/ms142547.aspx

http://msdn2.microsoft.com/en-us/library/ms189760.aspx

|||

Thanks a lot...

i got the full text indexes for my tables.

But do you suggest me to create a productstableadapter that uses FREETEXT SQL query.

or just call it from the function.

Thanks

|||

There are samples provided by Microsoft but I think you should use a stored proc like the samples in my first post and call the stored proc in your ADO.NET code. Hope this helps.

|||

Thank you again...

how do i pass search query entered by user.

SELECT ProductName
FROM Products
WHERE FREETEXT (ProductName, 'spread' )

instead of spread i would like to pass user input.

thanks once again 

|||

Then the CONTAINS predicate is more suited for your needs because it can be used in seven or more context when searching, run a search for all four predicates I posted in my first reply in SQL Server BOL(books online). Here are all the uses of CONTAINS. Hope this helps.

A. Using CONTAINS with <simple_term>

B. Using CONTAINS and phrase in <simple_term>

C. Using CONTAINS with <prefix_term>

D. Using CONTAINS and OR with <prefix_term>

E. Using CONTAINS with <proximity_term>

F. Using CONTAINS with <generation_term>

G. Using CONTAINS with <weighted_term>

H. Using CONTAINS with variables

|||

Hey Caddre (my favorite poster)

I want to piggy back on this. I implemented the code used in the links above but something still isn't right. Here's my code:

<code>

Sub search(ByVal sender As Object, ByVal e As System.EventArgs)
GridView1.Visible = True
Dim DBCOnnection As SqlConnection
Dim Criterion As String = Request.QueryString("Criterion")
DBCOnnection = New SqlConnection("Data Source=server/db;Initial Catalog=SOS_KnowledgeBase;Integrated Security=True")
DBCOnnection.Open()

Dim strSQL As String
Dim objDataset As New DataSet()
Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter()

strSQL = "exec dbo.sp_search @.Application, @.Category, @.textsearch"

objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, DBCOnnection)
objAdapter.SelectCommand.Parameters.AddWithValue("@.Application", DDLApp.SelectedItem.Text)
objAdapter.SelectCommand.Parameters.AddWithValue("@.Category", DDLCat.SelectedItem.Text)
objAdapter.SelectCommand.Parameters.AddWithValue("@.textsearch", textsearch.Text)
objAdapter.Fill(objDataset)
Dim oView As New DataView(objDataset.Tables(0))
GridView1.DataSource = oView
GridView1.DataBind()

DBCOnnection.Close()
If GridView1.PageCount <= 0 Then
Label1.Text = "No Results Matched Your Query."
Else

If GridView1.PageCount > 0 Then
Label1.Text = ""
End If

End If
Button1.Visible = False
Button2.Visible = True


End Sub

</code>

<html>

<asp:content id="Content1" contentplaceholderid="headertext" runat="server">

<asp:DropDownList ID="DDLApp" runat="server" DataTextField="Application" DataValueField="Application" AutoPostBack="True" DataSourceID="Application" AppendDataBoundItems="True" OnSelectedIndexChanged="refresh">
<asp:ListItem Value="0">-- Choose an Application --</asp:ListItem>
</asp:DropDownList><br />

<asp:SqlDataSource ID="Application" runat="server" ConnectionString="<%$ ConnectionStrings:SOSKB %>"
SelectCommand="SELECT DISTINCT Application.Application, Article.ApplicationID FROM Application INNER JOIN Article ON Application.ApplicationID = Article.ApplicationID">
</asp:SqlDataSource>
<asp:DropDownList ID="DDLCat" runat="server" DataTextField="Category"
DataValueField="Category" DataSourceID="Category" AppendDataBoundItems="True">
<asp:ListItem Value="0" Selected="True">-- Select All --</asp:ListItem>
</asp:DropDownList><asp:SqlDataSource ID="Category" runat="server" ConnectionString="<%$ ConnectionStrings:SOSKB %>"
SelectCommand="SELECT DISTINCT Category.Category FROM Category INNER JOIN Article ON Category.CategoryID = Article.CategoryID INNER JOIN Application ON Article.ApplicationID = Application.ApplicationID WHERE (Application.Application = ISNULL(@.Application, Application.Application)) ORDER BY Category.Category">
<SelectParameters>
<asp:ControlParameter ControlID="DDLApp" Name="Application" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>

<asp:Label ID="textsearch" runat="server" CssClass="box-title" Text="Keyword(s)"></asp:Label><br />
<asp:TextBox ID="searchbox" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="Button1" runat="server" OnClick="search" Text="Search Now" Width="83px" />

</asp:content>

</html>

<stored proc>

CREATE PROCEDURE [sp_search]

@.Application nvarchar (25),
@.Category nvarchar(25),
@.textsearch nvarchar(100)

AS

Begin

If @.Category ='-- Select All --'
SELECT Article.ArticleID, Application.Application, Category.Category,
Article.Title, Article.DateUpdated AS [Last Updated]
FROM Article INNER JOIN Application ON
Article.ApplicationID = Application.ApplicationID
INNER JOIN Category ON Article.CategoryID = Category.CategoryID
WHERE Application.Application = ISNULL(@.Application, Application.Application) AND
Category.Category = ISNULL(null, Category.Category) AND
Article.Title IN (
SELECT Title
From Article
WHERE (CONTAINS(*, @.textsearch)))

If @.@.RowCount=0
--raiserror ("No Results.", 16, 1)
RETURN @.@.Error
End
GO

</stored proc>

Here's the error I keep getting:

System.Data.SqlClient.SqlException was unhandled by user code
Class=15
ErrorCode=-2146232060
LineNumber=13
Message="Syntax error occurred near '('. Expected '' in search condition 'Keyword(s)'."
Number=7631
Procedure="sp_search"
Server="my server"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.ProviderBase.DataReaderContainer.Read()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at ASP.search_aspx.search(Object sender, EventArgs e) in D:\Documents and Settings\uid\My Documents\Visual Studio 2005\WebSites\SOSKnowledgeBase\search.aspx:line 33
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Can you tell me what I am doing wrong here? Thanks.

Cordell

|||

Never mind...so stupid. I was referencing a label instead of the actual textbox!!!!!

Now that this is fixed, does anyone know a link or two that shows me how to strip commas, periods, etc from textbox entries before you pass it to a procedure?

Cordell

|||

I think you are calling Contains too late in your query in the WHERE clause of a JOIN which is just a filter and if you are in Express there is limited support for Express. You should check out the other three predicates especially the tables version. Hope this helps.

FreetextTable

http://msdn2.microsoft.com/en-us/library/ms177652.aspx

Containstable

http://msdn2.microsoft.com/en-us/library/ms189760.aspx

And thanks for the compliment.

Full text Search doesnt find a specific string

Hi all,
We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case

select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"')
even though there exist records that satisfy the condition in the table,
the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"

If I remove the last 1 from the search string i get lot of records, Can anybody help me out.Hi all,
We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case

select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"')
even though there exist records that satisfy the condition in the table,
the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"

If I remove the last 1 from the search string i get lot of records, Can anybody help me out.

Im guessing here, but I think it is looking for records having one or more "words" that start with 1 (e.g. 1hs, 1tv, 1rquut). I'm not certain that the process would find just "1").

That's my story and I'm sticking with it.

Regards,

hmscott|||Thanks for your suggestion. Could you do me one more favor and modify the SQL query that I had put up in the previuos post to return the record that I am talking about.sql

Full text query contained only ignored words

Hello All,
Please explain this Fulltext behavior to me.
We have an asp application that is using sql that returns the above error
when you search for the word can't. If you search for "don't" or "Doesn't"
or pretty much anything else with an apostrophe, results are returned.
The same thing happens when you search for a decimal number. If I search
for 4.0, I get the above error. If I search for 4.00.0 I will get results.
Thanks in advance!
Jake
"GitarJake" <spamaintme@.oz.net> wrote in message
news:C0vRe.101151$Ep.81169@.lakeread02...
> Hello All,
> Please explain this Fulltext behavior to me.
> We have an asp application that is using sql that returns the above error
> when you search for the word can't. If you search for "don't" or
> "Doesn't" or pretty much anything else with an apostrophe, results are
> returned.
> The same thing happens when you search for a decimal number. If I search
> for 4.0, I get the above error. If I search for 4.00.0 I will get
> results.
Check the noise.X file in your FTDATA\Config folder (where X is the
extension related to your word breaker language - eng for English, enu for
US English, etc).
4 is in the list. I'm guessing that when 4.0 is passed FTS treats it a
number and so finds it in the noise word file, whereas when 4.00.0 is used
it's obviously not a number and so does a search for it as is.
Can is a word in the noise word list too. I'm guessing FTS splits can't into
can and not internally, and errors because can is in the list.
What you could do is empty the noise word file - leave only a single line in
it with a single space on it. Then run a full population. This has the
effect of not erroring on any words (although some characters that are used
as word breakers will still throw the noise words error, you'll have to
filter these out in your query builder).
Dan
|||You might want to review the following
http://www.indexserverfaq.com/noise.htm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GitarJake" <spamaintme@.oz.net> wrote in message
news:C0vRe.101151$Ep.81169@.lakeread02...
> Hello All,
> Please explain this Fulltext behavior to me.
> We have an asp application that is using sql that returns the above error
> when you search for the word can't. If you search for "don't" or
"Doesn't"
> or pretty much anything else with an apostrophe, results are returned.
> The same thing happens when you search for a decimal number. If I search
> for 4.0, I get the above error. If I search for 4.00.0 I will get
results.
>
> Thanks in advance!
> Jake
>
|||Hi Daniel,
Thanks for responding.
I have removed "can" from both the fulltext noise.enu word list in
ftdata\config; and the Indexing noise.enu. The wordbreaker in FTI is
neutral. I completely deleted the FT catalogs and manually recreated and
repopulated them. The results were the same.
Then I deleted the contents FTI's noise.enu except for a space and now, the
search simply does not return results. It seems like I can search for the
word can't on any other search engine but ours. Below is the query we are
passing using just the word *can't*. Can you tell if there is anything
within the query that might be better able to produce results?
select ARTICLE_ID, ARTICLE_STITLE, ARTICLE_LTITLE, ARTICLE_SUMMARY
,ARTICLE_STATUS, ARTICLE_APPROVED, DEPART_REF, USER_REF_OWNER,
USER_REF_EDITOR, ARTICLE_REVIEW_DATE, ARTICLE_EXPIRE, RANK,
PUBLIC_DIVISION_REF From ARTICLES as A, containstable(ARTICLES, *, '
"can''t" ') as AST where (LANGUAGE_REF= 1 and ARTICLE_DISABLE_SEARCH <> 1 )
and AST.[KEY] = A.ARTICLE_ID order by RANK desc
MIS query: "can''t"
TIA
Jake
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23BYyg8vrFHA.3096@.TK2MSFTNGP15.phx.gbl...
> "GitarJake" <spamaintme@.oz.net> wrote in message
> news:C0vRe.101151$Ep.81169@.lakeread02...
> Check the noise.X file in your FTDATA\Config folder (where X is the
> extension related to your word breaker language - eng for English, enu for
> US English, etc).
> 4 is in the list. I'm guessing that when 4.0 is passed FTS treats it a
> number and so finds it in the noise word file, whereas when 4.00.0 is used
> it's obviously not a number and so does a search for it as is.
> Can is a word in the noise word list too. I'm guessing FTS splits can't
> into can and not internally, and errors because can is in the list.
> What you could do is empty the noise word file - leave only a single line
> in it with a single space on it. Then run a full population. This has the
> effect of not erroring on any words (although some characters that are
> used as word breakers will still throw the noise words error, you'll have
> to filter these out in your query builder).
> Dan
>
|||To test this I added "Can't you see?" to the end of a book title in the pubs
database. I then created a new catalog on the title column, ran a full
population, and then ran this:
select * from titles where contains(title,'can''t')
and up popped the row I'd added the phrase to. I then tried this:
select a.* from titles a ,containstable(titles,*,'can''t') b where
a.title_id = b.[key]
select a.* from titles a ,containstable(titles,*,'"can''t"') b where
a.title_id = b.[key]
and again, I get the same row back from both versions. So it's not a problem
with the apostrophe, nor an issue with the double quotes.
The only other thing in your query that will prevent the row from appearing
will be if ARTICLE_DISABLE_SEARCH = 1 or LANGUAGE_REF <> 1. Have you tried
running the query with these clauses removed just in case?
Dan
GitarJake wrote on Sun, 4 Sep 2005 12:33:07 -0400:
[vbcol=seagreen]
> Hi Daniel,
> Thanks for responding.
> I have removed "can" from both the fulltext noise.enu word list in
> ftdata\config; and the Indexing noise.enu. The wordbreaker in FTI is
> neutral. I completely deleted the FT catalogs and manually recreated and
> repopulated them. The results were the same.
> Then I deleted the contents FTI's noise.enu except for a space and now,
> the search simply does not return results. It seems like I can search for
> the word can't on any other search engine but ours. Below is the query we
> are passing using just the word *can't*. Can you tell if there is
> anything within the query that might be better able to produce results?
> select ARTICLE_ID, ARTICLE_STITLE, ARTICLE_LTITLE, ARTICLE_SUMMARY
> ,ARTICLE_STATUS, ARTICLE_APPROVED, DEPART_REF, USER_REF_OWNER,
> USER_REF_EDITOR, ARTICLE_REVIEW_DATE, ARTICLE_EXPIRE, RANK,
> PUBLIC_DIVISION_REF From ARTICLES as A, containstable(ARTICLES, *, '
> "can''t" ') as AST where (LANGUAGE_REF= 1 and ARTICLE_DISABLE_SEARCH <>
> 1 ) and AST.[KEY] = A.ARTICLE_ID order by RANK desc
> MIS query: "can''t"
> TIA
> Jake
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message news:%23BYyg8vrFHA.3096@.TK2MSFTNGP15.phx.gbl...