Full text Substring search not working

I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.

If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;

But it works if i do
select * from testtable where contains(description,'"Exting*"') ;

The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.SQL Server doesn't support leading wildcards. Therefore your first query will be searching on "xting*" explaining why no results are returned.

I'm not sure why MS does this. Perhaps request it for SQL2008!!

HTH.

|||Doign a short talk with a fellow MVP SimonS, this is due to the Wordbreakers which are language specified. They can only chop the words according to known word breaking settings. So it depends whether a compound word can be broken into multiple ones. Searching within the word is that case not possible, searching for the words within a compound is possible if the wordbreaker can identity the appropiate individual words.

Jens K. Suessmeyer

http://www.sqlserver2005.de

full text special characters

I am trying to modify my noise word list and found that there are special characters that are ignored no matter what... I need to be able to search on these characters (well at least some of them) so I need to some up with a way to get them into the catalog.

I am running SQL 2000 so using the thesaurus is out (feature not available) and microsoft does not recomment using the thesaurus for special charaters anyways.

SO what I was thinking of was to when I build the data for the catalog, I use replace the special characters with equivalent words. Does this sound like it would work?

Then the procedure that does the searching would also have to look for the special characters and replace them as well....

Does anyone have a better idea?

Also is there any place that I can get the full list of special characters? Here is the one I have.

Apostrophe

'

Accent grave

`

Number sign

#

Percent sign

%

Greater than

>

Less than

<

Exclamation mark

!

Period

.

Brackets

[ or ]

Asterisk

*

Dollar

$

Semicolon

;

Colon

:

Question mark

?

Caret

^

Braces

( or )

Plus sign

+

Backslash

\

Hey, I am unfortunatley unable to provide you with any real feedback reguarding the nature of your problem. However I am a Instructional Technologist designing Aids for users at my current place of work and was hoping you might be able to point me into the right direction as far as the Full-TExt search function goes within SQL.

I need to find out the idiosyncracies of the search so that I can later train on it.

That is, I know that if I search for (arbitrary) form "AD44445" by entering AD I will get a long list of forms that reflect this title,

AD34563

AD42034

AD23404

AD etc etc etc

However I need to know what other pnumonics the search engine relies on to gather the necessary information of the searched item.

If there is a manual that is not technical that tells users why the full text search works the way it does could you please point me in the way of it?

sql

Full text search on TEXT field

I've a problem. I'm inexperienced about FTS.
I have a TEXT datatype field in my table and I store MS Word documents.

I want to do full text search on this TEXT datatype field.
So I build catalog and run

"Select * from table where Contains (textfield,'Yusuf')" sql in analyzer.

but null value returns. however I know the table has 3 records.

Anyway, I insert a varchar field in the table and edit my fts catalog.
and then insert 3 records in the table.than rebuild catalog and I try
"Select * from table where Contains (varcharfield,'Yusuf')"
this sql returns true records.

I know that I can FTS in TEXT datatype field but I can't. I don't know why.
I need your help.
thanks alot

YusufBesides,
I insert text in text datatype field and returs true values...

The problem occours when insert only MS Word or MS Excel etc files.

Full Text Search on Patterns in SQL Server 2005

Can the full text search in SQL Server 2005 handle cases like a wildcard in
the middle of a word?
--
WillHi Will,
Since SQL Server 2005 is not released yet, I could recommand you posting
here
http://forums.microsoft.com/msdn/default.aspx?ForumGroupID=19
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

full text search on doc files

I have a list of word files (.doc) I am trying to us the full text
search in sql server to find key words in these word documents. I have
a Windows XP and sql server installed on my machine. ( no network
server). I cant seem to get the code to work to do a search on these
files. I would really appreciate if someone can send me some code from
start to finsh so I can create the sql tables and then catolog to
search for keywords in the word files.What version of SQL Server are you using? Word documents must be stored in
columns of data type image, and another column must be present to store the
type suffix (".doc" for MS Word files).
When full-text indexing is activated for an image column the name of the
column where the suffix is stored must also be set. How this is actually don
e
depends on the SQL Server version.
SQL 2000:
sp_fulltext_column
@.tabname = '<qualified_table_name>'
,@.colname = '<column_name>'
,@.action = 'add'
,@.type_colname = '<type_column_name>'
SQL 2005:
create fulltext index on <table_name>
(<column_name> TYPE COLUMN <type_column_name>
key index <index_name>
If the index has been created appropriately, then you might also check the
SQL Error Log:
http://milambda.blogspot.com/2005/1...n-or-bybug.html
ML
http://milambda.blogspot.com/|||ML,
How do i create the table first and get the files indexed? How do I
get the files catologued? i am using sql2000 . Do i run the sql you
wrote above after the files have been imported?|||Setting up full-text indexing in SQL 2000 is very well documented in Books
Online:
http://msdn.microsoft.com/library/d... />
d_6g1f.asp
After you've created the table and enabled the database for full-text
search, you need to enable the table, then the column for full-text indexing
.
It really doesn't matter whether you set up FTI before inserting data in the
tables, or after, as long as you propagate change to the catalogue before yo
u
try to use full-text search (new rows will not be found by the engine until
the contents have been indexed).
There are several methods of propagating change to the full-text catalogue:
full propagation, incremental propagation and background propagation (with
change tracking). With background propagation new rows are indexed after
they've been inserted and changed rows are indexed after they've been
updated, but there's a performance impact and writetext/updatetext operation
s
are not detected.
To avoid the downside you could use incremental propagation by creating a
SQL Agent Job that starts incremental propagation as frequently as you need.
Are you having any specific problems? You could also ask these questions in
the dedicated newsgorup: "microsoft.public.sqlserver.fulltext".
ML
http://milambda.blogspot.com/|||ML,
I have looked through the link, still not getting it to work. I guess
i need some code, from start to finish, loading the file from the
c:\... and then running the sql. Anything you may have would be
extremely helpful..thanks|||The link I sent you will help you set up full-text indexing, but the actual
method of inserting MS Word documents into the table depends on your client
application.
If you need to insert blobs through T-SQL, you could use BULK INSERT:
http://msdn.microsoft.com/library/d...br />
4fec.asp
Also make sure you create the format file in accordance to this KB article:
http://support.microsoft.com/defaul...kb;en-us;271344
Information on using format files:
http://msdn.microsoft.com/library/d...>
bcp_9yat.asp
ML
http://milambda.blogspot.com/

Full Text Search Failed for the word 'is'

I am implementing a full text search. When I search for the word 'is', it is
giving a wrong result. I need the help to solve this problem.
either do a freeText search, which will in most cases prevent this error, or
remove is from your noise word list.
Hilary
"narayananst" <narayananst@.discussions.microsoft.com> wrote in message
news:EF9989EB-A396-4F22-BCBA-696133B24D51@.microsoft.com...
>I am implementing a full text search. When I search for the word 'is', it
>is
> giving a wrong result. I need the help to solve this problem.
|||Narayananst,
The word "is" is typically a "noise word" or sometimes referred to as a stop
word that is contained in a language-specific noise word file, as it is so
common in the English language that it provide no significant help in Full
Text Search. However, you can edit or removed from the language specific
noise.* file (noise = US_English) with notepad.exe.
These files are located under \FTDATA\SQLServer\Config\noise.* where *
represents the language - enu = US_English. You will need to stop the
MSSearch service, edit noise.enu (assuming US_English) with notepad.exe and
remove these letters and save the file, restart the MSSearch service and run
a Full Population on all of your FT Catalogs. I'd recommend that you remove
"is" and other noise words that you may want to search on, but not empty the
entire file or at least leave a single space in the file.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"narayananst" <narayananst@.discussions.microsoft.com> wrote in message
news:EF9989EB-A396-4F22-BCBA-696133B24D51@.microsoft.com...
> I am implementing a full text search. When I search for the word 'is', it
is
> giving a wrong result. I need the help to solve this problem.

full text search : Search orders of single input of "multiple word

Sql server express advanced edition
Hi
I am using following query to select data using full text search in a stored
procedure:
=====================
SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
Select *
from tablename1
where contains (*, @.keyword)
===========================
It returns me rows for keyword "Fish Chips" which have values (Fish and
chips) but when i change my search keyword to "Chips Fish" it does not return
any rows.
Can I modify my query so that It returms me all the rows containing Fish or
chips in any order?
i.e. In the input screen for users they input a combination of words like
"chinese mexican". My query should spit all rows which have chinese and
mexican in any order in one of indexed columns.
For above input (Chinese mexican) I should have output like :
Chinese mexican food
chinese and mexican food
mexican and chinese food
mexican chinese clothing
mexican food indian food and chinese food
If I need to parse input and divide it in multiple words before searching,
If someone has a example on how to do this, will be great.
ontario, canada
In other words, I have requirement where I need to do full text search on
all the
search text. For example, for the search text "fish chips" , I should get
the following records having "fish chips" as well as "chips fish".
ontario, canada
"db" wrote:

> Sql server express advanced edition
> Hi
> I am using following query to select data using full text search in a stored
> procedure:
> =====================
> SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
> Select *
> from tablename1
> where contains (*, @.keyword)
> ===========================
> It returns me rows for keyword "Fish Chips" which have values (Fish and
> chips) but when i change my search keyword to "Chips Fish" it does not return
> any rows.
> Can I modify my query so that It returms me all the rows containing Fish or
> chips in any order?
> i.e. In the input screen for users they input a combination of words like
> "chinese mexican". My query should spit all rows which have chinese and
> mexican in any order in one of indexed columns.
> For above input (Chinese mexican) I should have output like :
> Chinese mexican food
> chinese and mexican food
> mexican and chinese food
> mexican chinese clothing
> mexican food indian food and chinese food
> If I need to parse input and divide it in multiple words before searching,
> If someone has a example on how to do this, will be great.
>
> --
> ontario, canada

Full Text Search (2005) - How to determine word offset in CONTAINS query?

Does anyone know if it is possible to determine the relative word offset (the Occ) from a simple-term query such as:-

SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments, ' "mountain biking" ');

So, given the text:-

"Maybe it's just because I'm new to mountain biking, but I had a terrible time getting used to these pedals."

I would like the query would return both the text and the word offset of 8. To me, it seems like this would be quite useful as I want to highlight the found text for the user to see. Obviously I can do a post-SELECT scan of the string to find the values but this would seem unnecessary.

If anyone can give me any pointers I'd be very grateful.

Thanks

You may want to post this on the database engine forum (for full-text search) - http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=93 since this is not a SQL Server Data Mining feature.|||I think I will, but I wasn't sure where to post the question as there isn't an FTS forum (yet!). Thanks for your suggestion.sql

full text body search not finding all results

I am running SQL 2005 SP1 and I have a database I'm using for GFI Mail
Archiver. When I run queries against the database for a specific word i.e
"testing", only 2 results show up when there should be more results.
I know the data is there because I can see it, it's just that when I do a
search it doesn't find it. I have full text-catalog enabled on the database.
I've tried deleting and re-creating the catalog but still the same thing.
The question is are they really there or are they being indexed.
Use a like to verify that it is really there. If it is then it is a problem
with it being indexed. Review the gatherer logs to see if it indexes the
rows correctly.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:1813FDF8-E095-4784-B9B8-707C8309B1C9@.microsoft.com...
>I am running SQL 2005 SP1 and I have a database I'm using for GFI Mail
> Archiver. When I run queries against the database for a specific word i.e
> "testing", only 2 results show up when there should be more results.
> I know the data is there because I can see it, it's just that when I do a
> search it doesn't find it. I have full text-catalog enabled on the
> database.
>
> I've tried deleting and re-creating the catalog but still the same thing.
|||That's what my problem is, it's not being indexed correctly.
How do I fix that?
"Hilary Cotter" wrote:

> The question is are they really there or are they being indexed.
> Use a like to verify that it is really there. If it is then it is a problem
> with it being indexed. Review the gatherer logs to see if it indexes the
> rows correctly.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:1813FDF8-E095-4784-B9B8-707C8309B1C9@.microsoft.com...
>
>
|||Can you see if there are any errors in the gatherer logs? If not can you
send some of the problem docs to me offline or post them here? Send me the
originals before they went into the database.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:5FEDEB29-BE51-4733-8224-58C87AEAED5C@.microsoft.com...[vbcol=seagreen]
> That's what my problem is, it's not being indexed correctly.
> How do I fix that?
> "Hilary Cotter" wrote:
|||I'm new to SQL so I'll need some help here.
1. How do I get gatherer logs?
2. When you say problem docs, what do you mean exactly? (these are test
emails).
"Hilary Cotter" wrote:

> Can you see if there are any errors in the gatherer logs? If not can you
> send some of the problem docs to me offline or post them here? Send me the
> originals before they went into the database.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:5FEDEB29-BE51-4733-8224-58C87AEAED5C@.microsoft.com...
>
>
|||You can find the gatherer logs on a SQL 2005 server in C:\Program
Files\Microsoft SQL Server\MSSQL.X\MSSQL\LOG>
Where X is your instance name.
The gatherer logs themselves will look like this:
SQLFT0001000015.LOG
test email should be indexable in a char or varchar column. Attachments may
not be depending on how you store them. Are you storing them in varbinary or
image columns? Do you have a document type column associated with the image
or varbinary columns?
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:1F6F0D54-F9FB-4CC3-A8D3-E0445883AE00@.microsoft.com...[vbcol=seagreen]
> I'm new to SQL so I'll need some help here.
> 1. How do I get gatherer logs?
> 2. When you say problem docs, what do you mean exactly? (these are test
> emails).
> "Hilary Cotter" wrote:
|||Ok, I'll look at them. I assume I just open them with Notepad. What am I
looking for?
As far as your questions:
1. Not sure if they're being stored in verbinary or image columns. However
this GFI Mail Archive stores it.
2. Don't know if I have a document type column associated with the
verbinary or image columns.
"Hilary Cotter" wrote:

> You can find the gatherer logs on a SQL 2005 server in C:\Program
> Files\Microsoft SQL Server\MSSQL.X\MSSQL\LOG>
> Where X is your instance name.
> The gatherer logs themselves will look like this:
> SQLFT0001000015.LOG
> test email should be indexable in a char or varchar column. Attachments may
> not be depending on how you store them. Are you storing them in varbinary or
> image columns? Do you have a document type column associated with the image
> or varbinary columns?
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:1F6F0D54-F9FB-4CC3-A8D3-E0445883AE00@.microsoft.com...
>
>
|||Can you script out your table, indexes, and full text indexes and post them
here.
Key to solving your problem is discovering how the GFI archive stores it. If
it is text, or msg you should be fine. If it is something proprietary you
will be unable to index them.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:BD072397-C5D4-4B19-A8E6-80C03AF1784E@.microsoft.com...[vbcol=seagreen]
> Ok, I'll look at them. I assume I just open them with Notepad. What am I
> looking for?
> As far as your questions:
> 1. Not sure if they're being stored in verbinary or image columns.
> However
> this GFI Mail Archive stores it.
> 2. Don't know if I have a document type column associated with the
> verbinary or image columns.
> "Hilary Cotter" wrote:
|||I went to the GFI web site and notice that their product does work with SQL
FTS. Their technical support group should be able to help you through this
problem.
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
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23lkK28IEHHA.3520@.TK2MSFTNGP04.phx.gbl...
> Can you script out your table, indexes, and full text indexes and post
> them here.
> Key to solving your problem is discovering how the GFI archive stores it.
> If it is text, or msg you should be fine. If it is something proprietary
> you will be unable to index them.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:BD072397-C5D4-4B19-A8E6-80C03AF1784E@.microsoft.com...
>

Ful Text Search

People,

I'm working with Full-Text Indexing on SQL 2000 SP3. And like to use the Default Language for word Breaker = Portuguese.

Or something like this.

I have already tried on Technet and MSDN Web Site.
But didn't have success.

Suggestions??

Thanks a lot.See page 39
http://www.microsoft.com/israel/msdn/downloads/netweb/SISiteServer.ppt

I was searching for it some time before. And that is all I have found ;)|||I had already found this document too.

But using the Neutral word breaker I don't get Verbal functions in my language. (Portuguese Brazil).

Thanks a Lot!

FTS- not returning results when lines are searched.

Hello everyone,
I am performing full-text-search on a table containing word documents
in it's column. The search results fair when a single word is
searched. i.e. when I fire the following query:
select * from docs where contains(document, '"java"');
but if I try to search the a full line or give multiple words in the
text to be searched, then it shows no results, even if there is same
text present in the document stored in the database.
select * from docs where contains(document, '"this text is in
database"');
what could be the possible cause of the problem. Shouldn't the FTS
return the records which contains even a single word in the queried
text.
Please help me with the issue.
Thanks & Regards,
Varun Narang.
It definitely should return results for both a single token or a phrase.
However in your phrase you have noise words? Did you empty your noise word
list and replace it with a single space?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Varun" <varunarang@.gmail.com> wrote in message
news:1142867652.058844.39330@.g10g2000cwb.googlegro ups.com...
> Hello everyone,
> I am performing full-text-search on a table containing word documents
> in it's column. The search results fair when a single word is
> searched. i.e. when I fire the following query:
> select * from docs where contains(document, '"java"');
> but if I try to search the a full line or give multiple words in the
> text to be searched, then it shows no results, even if there is same
> text present in the document stored in the database.
> select * from docs where contains(document, '"this text is in
> database"');
> what could be the possible cause of the problem. Shouldn't the FTS
> return the records which contains even a single word in the queried
> text.
> Please help me with the issue.
> Thanks & Regards,
> Varun Narang.
>
|||Varun wrote on 20 Mar 2006 07:14:12 -0800:

> Hello everyone,
> I am performing full-text-search on a table containing word documents
> in it's column. The search results fair when a single word is
> searched. i.e. when I fire the following query:
> select * from docs where contains(document, '"java"');
> but if I try to search the a full line or give multiple words in the
> text to be searched, then it shows no results, even if there is same
> text present in the document stored in the database.
> select * from docs where contains(document, '"this text is in
> database"');
> what could be the possible cause of the problem. Shouldn't the FTS
> return the records which contains even a single word in the queried
> text.
Because you have double quotes around the phrase, it will only look for rows
with that exact phrase in the text. If you want to search for any/all words
in any order and not necessarily in a single phrase, you need to remove
those double quotes. Also check BOL for the use of logical operators.
Dan