Full Text Search Problem

I wanna search for cars thats are Ferrari,_NAME and _TYPE fields are full text indexed

SELECT

COUNT(*)FROMCONTAINSTABLE(ADDS,*,'Ferrari AND Cars') >>> return only 8 results

but in facts there are 600 results.Doesn't "*" search in all coloumns?

********************************

Do I have to make a query like this...

SELECT

Count(T1.[KEY])FROMCONTAINSTABLE(ADDS,_NAME,'Ferrari')AS T1>>>>>return 600 results.

INNER

JOINCONTAINSTABLE(ADDS,_TYPE,'Cars')AS T2

ON

T1.[KEY]=T2.[KEY]

I don't want this type of query as I don't know how many parameters I will have and I am sick of the joins which slow down the server.

Your first query should work. Can you post a sample row which you think should satisfy the CONTAINSTABLE filter but is?not?count?|||

FIRST QUERY RESULTS

ID----NAME ------TYPE

2 Ferrari and Cars null

SECOND QUERY RESULTS

ID----NAME ------TYPE

24 Ferrari Car........................missing rows

24 Ferrari is a car null

24 null ferrari Cars are faster

|||Then I guess you need to use OR operator, not AND. As Ferrari is?in?a?different?field?than?where Car resides for "missing row".

SELECT
COUNT(*) FROM CONTAINSTABLE(ADDS,*,'"Ferrari" OR "Car"')|||

than I have to put a full text index in all column in the table and use OR clause and reverse rank it for AND clause,using CONTAINSTABLE function.

but in that case I wil retrieve all the unneccessary data from the database.

what about building a table like _ID,VALUE....and in the value field I put the _NAME and _TYPE of the car and full text index that table.

Does this perform better than using "OR"?

|||That's a good idea. If so, are you sure you need Full-Text search? Why not just use LIKE operator on some columns to filter rows??For example:

SELECT
COUNT(*) FROM NAME Like'Ferrari%' AND?TYPE?LIKE '%Car%'

Pay more attention to table schema/relation design will improve your database performance.|||

are you sure you need Full-Text search?

----

As far as ? know >>>>>>>>LIKE '%Car%' can not use indexes in sql 2005 so it much slower than searching tokens in a document using full text.

Do u know any way to see how the sql server uses the execution plan?How can I see which process uses the most RAM?

|||Yes you're right, LIKE 'Car%' can use Indexes, while LIKE '%Car%' can't. However personally I often use LIKE operator instead of Full-Text search if possible.?

There are several ways to check execution plan in SQL2005:

1.?SET?SHOWPLAN?ALL?ON,?which?will?causes Microsoft? SQL Server? not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.

2. CaptureSQL Profiler trace with proper Performance events( such as Show Plan All)

3.?Use?new?powerfulExecution Related Dynamic Management Views and Functions

Here is a great article for overall Performance T-Shooting in SQL2005:
Troubleshooting Performance Problems in SQL Server 2005

Enjoy SQL2005!|||

thanks for all the links but esppecially the 3 rd one but I am unable to use

sys.dm_exec_background_job_queue

sys.dm_exec_background_job_queue_stats

sys.dm_exec_cached_plans

sys.dm_exec_connections

sys.dm_exec_cursors

sys.dm_exec_plan_attributes

sys.dm_exec_query_memory_grants

sys.dm_exec_query_optimizer_info

sys.dm_exec_query_plan

sys.dm_exec_query_resource_semaphores

sys.dm_exec_query_stats

sys.dm_exec_requests

sys.dm_exec_sessions

sys.dm_exec_sql_text

these methods I have used it like

SELECT * FROM sys.dm_exec_connections....but the resultset has no rows...

Do u have an idea why that happens?

|||That means currently no user connection has been established to the SQL instance. Where did you execute the query?|||hmmmm ok. some of them work so I must be missing something thanks anyway.

Full Text Search Engine and russian language support...

I try to develope application with full text search in SQl tables by fields, that containing russian text. I had to use "neutral" for language for word breaker.
Is there are russian addon's for MS SQL 2000, that can solve this problem? Would be included in MS SQL 2005 russian language support? If not - how can I solve it by other way?

You may want to post this on the database engine forum (for full-text search) - http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=93

Full Text Query Problem

Hi
We have a full text index on tblPages, the two fields that we wish to search
for 'server' are Symtoms and Whilst.
The problem is that we were running the join in the WHERE clause, but we
were not getting distinct results where 'server' was in both symtoms and
whilst.
So we have changed the join to an inner join, the only problem is that with
the current setup it is joining tblPages > A > B where as we want:
tblPages > A
> B
Any advice would be much appreciated.
Thanks
B
SELECT distinct(pageref), tblPages.Location, tblPages.Symptoms,
tblPages.Whilst, A.RANK, B.RANK
from tblPages AS tblPages
INNER JOIN containstable(tblPages, Symptoms, 'server') as A on
A.[KEY] = tblPages.PageRef
INNER JOIN containstable(tblPages, Whilst, 'server') as B ON B.[KEY]
= tblPages.PageRef"Ben" <Ben@.NoSpam.com> wrote in message
news:Ou7X2thUFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a full text index on tblPages, the two fields that we wish to
> search for 'server' are Symtoms and Whilst.
> The problem is that we were running the join in the WHERE clause, but we
> were not getting distinct results where 'server' was in both symtoms and
> whilst.
> So we have changed the join to an inner join, the only problem is that
> with the current setup it is joining tblPages > A > B where as we want:
> tblPages > A
> Any advice would be much appreciated.
> Thanks
> B
> SELECT distinct(pageref), tblPages.Location, tblPages.Symptoms,
> tblPages.Whilst, A.RANK, B.RANK
> from tblPages AS tblPages
> INNER JOIN containstable(tblPages, Symptoms, 'server') as A on
> A.[KEY] = tblPages.PageRef
> INNER JOIN containstable(tblPages, Whilst, 'server') as B ON
> B.[KEY] = tblPages.PageRef
DISTINCT works across all columns, you can't specify it to only be for one
column, so those parentheses around pageref actually do nothing and are
ignored by the parser. If you have non-unique pageref values in the results
then you need to use a GROUP BY clause on pageref and summation functions on
each row (eg. min, max, avg, etc) to tell SQL Server what you want returned
for each unique pageref value. However, if pageref is the primary key then
you will not need to do this using the following.
Anyway, onto the join. By specifying inner for both A and B you are limiting
the results to only those rows that have the term in both Whilst and
Symptoms. From the wording of your post you actually want rows that are in
either, for this you can use left outer joins.
SELECT distinct(pageref), tblPages.Location, tblPages.Symptoms,
tblPages.Whilst, A.RANK, B.RANK
from tblPages AS tblPages
LEFT OUTER JOIN containstable(tblPages, Symptoms, 'server') as A ON
tblPages.PageRef = A.[KEY]
LEFT OUTER JOIN containstable(tblPages, Whilst, 'server') as B ON
tblPages.PageRef = B.[KEY]
where A.[KEY] is not null OR B.[KEY] is not null
This will return all rows from tblPages where there is a match in either A
or B, or both.
You can also use EXISTS to give the same results, although I'm not sure on
the performance difference. I've read that EXISTS might be quicker on tables
with a large number of rows as rather than joining all rows to determine
which should be returned an index lookup can be performed to determine if a
row matches. I've run some tests on my own data and so far I've found left
joins and exists to be similar in performance, but on your data you may well
get different results.
Dan|||Thanks Dan
Thats ideal
B
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23wzMMIiUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> "Ben" <Ben@.NoSpam.com> wrote in message
> news:Ou7X2thUFHA.2420@.TK2MSFTNGP12.phx.gbl...
> DISTINCT works across all columns, you can't specify it to only be for one
> column, so those parentheses around pageref actually do nothing and are
> ignored by the parser. If you have non-unique pageref values in the
> results then you need to use a GROUP BY clause on pageref and summation
> functions on each row (eg. min, max, avg, etc) to tell SQL Server what you
> want returned for each unique pageref value. However, if pageref is the
> primary key then you will not need to do this using the following.
> Anyway, onto the join. By specifying inner for both A and B you are
> limiting the results to only those rows that have the term in both Whilst
> and Symptoms. From the wording of your post you actually want rows that
> are in either, for this you can use left outer joins.
> SELECT distinct(pageref), tblPages.Location, tblPages.Symptoms,
> tblPages.Whilst, A.RANK, B.RANK
> from tblPages AS tblPages
> LEFT OUTER JOIN containstable(tblPages, Symptoms, 'server') as A ON
> tblPages.PageRef = A.[KEY]
> LEFT OUTER JOIN containstable(tblPages, Whilst, 'server') as B ON
> tblPages.PageRef = B.[KEY]
> where A.[KEY] is not null OR B.[KEY] is not null
> This will return all rows from tblPages where there is a match in either A
> or B, or both.
> You can also use EXISTS to give the same results, although I'm not sure on
> the performance difference. I've read that EXISTS might be quicker on
> tables with a large number of rows as rather than joining all rows to
> determine which should be returned an index lookup can be performed to
> determine if a row matches. I've run some tests on my own data and so far
> I've found left joins and exists to be similar in performance, but on your
> data you may well get different results.
> Dan
>|||Hi Dan
Sorry, one more question, a little different this time.
The Containstable function works fine with single words, but problems occur
as I will allow users to add free text.
When there is more than one word then AND, OR, AND NOT is needed.
I can do some basic checking such as replacing double spaces and spaces with
AND (or OR), but if they enter a comma etc more problems occur.
Is there any solid method of doing this or should I use basic
checking/replace statments?
THanks
B
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23wzMMIiUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> "Ben" <Ben@.NoSpam.com> wrote in message
> news:Ou7X2thUFHA.2420@.TK2MSFTNGP12.phx.gbl...
> DISTINCT works across all columns, you can't specify it to only be for one
> column, so those parentheses around pageref actually do nothing and are
> ignored by the parser. If you have non-unique pageref values in the
> results then you need to use a GROUP BY clause on pageref and summation
> functions on each row (eg. min, max, avg, etc) to tell SQL Server what you
> want returned for each unique pageref value. However, if pageref is the
> primary key then you will not need to do this using the following.
> Anyway, onto the join. By specifying inner for both A and B you are
> limiting the results to only those rows that have the term in both Whilst
> and Symptoms. From the wording of your post you actually want rows that
> are in either, for this you can use left outer joins.
> SELECT distinct(pageref), tblPages.Location, tblPages.Symptoms,
> tblPages.Whilst, A.RANK, B.RANK
> from tblPages AS tblPages
> LEFT OUTER JOIN containstable(tblPages, Symptoms, 'server') as A ON
> tblPages.PageRef = A.[KEY]
> LEFT OUTER JOIN containstable(tblPages, Whilst, 'server') as B ON
> tblPages.PageRef = B.[KEY]
> where A.[KEY] is not null OR B.[KEY] is not null
> This will return all rows from tblPages where there is a match in either A
> or B, or both.
> You can also use EXISTS to give the same results, although I'm not sure on
> the performance difference. I've read that EXISTS might be quicker on
> tables with a large number of rows as rather than joining all rows to
> determine which should be returned an index lookup can be performed to
> determine if a row matches. I've run some tests on my own data and so far
> I've found left joins and exists to be similar in performance, but on your
> data you may well get different results.
> Dan
>|||"Ben" <Ben@.NoSpam.com> wrote in message
news:eyPByIjUFHA.2540@.tk2msftngp13.phx.gbl...
> Hi Dan
> Sorry, one more question, a little different this time.
> The Containstable function works fine with single words, but problems
> occur as I will allow users to add free text.
> When there is more than one word then AND, OR, AND NOT is needed.
> I can do some basic checking such as replacing double spaces and spaces
> with AND (or OR), but if they enter a comma etc more problems occur.
> Is there any solid method of doing this or should I use basic
> checking/replace statments?
There have been lots of posts in microsoft.public.sqlserver.fulltext with
solutions to this, many using regular expressions to quickly create clauses.
I myself use a lump of code I wrote about 10 years ago which deals with this
and parentheses and quoted phrases, but it's messy and I'd rather clean it
up before posting.
A search on google groups for fulltext parsing should pull up some useful
info, such as
http://groups.google.co.uk/group/mi...fulltext&hl=en
Dan
Dan|||Ben,
This is a somewhat common question in the fulltext newsgroup (I post replies
there often), and you may benefit from the following KB article: 246800
(Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defau...kb;EN-US;246800
You may want to alter the below Northwind FTS query to fit your environment,
as it handles FTS across (AND) columns:
use Northwind
go
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'BA') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, that you can alter the AND between the KEY columns to an OR condition
and then use distinct in your select list.
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ben" <Ben@.NoSpam.com> wrote in message
news:eyPByIjUFHA.2540@.tk2msftngp13.phx.gbl...
> Hi Dan
> Sorry, one more question, a little different this time.
> The Containstable function works fine with single words, but problems
occur
> as I will allow users to add free text.
> When there is more than one word then AND, OR, AND NOT is needed.
> I can do some basic checking such as replacing double spaces and spaces
with
> AND (or OR), but if they enter a comma etc more problems occur.
> Is there any solid method of doing this or should I use basic
> checking/replace statments?
> THanks
> B
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:%23wzMMIiUFHA.1044@.TK2MSFTNGP10.phx.gbl...
we
and
one
you
Whilst
ON
A
on
far
your
>

Full text indexing with Japanese characters problem

Hi all,
I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.
As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?
Thanks!
ibiza
I'm not sure about your problem, but could I have a copy of your DB?
That looks amazingly interesting I love japanese ^_^
|||well, if you can help me with that problem, I'd be glad to consider it

(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=
nce I
can find...
please help!
thanks
ibiza
|||well, if you can help me with that problem, I'd be glad to consider it

(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=
nce I
can find...
someone please help!
thanks
ibiza
|||Does text indexing support non-latin characters?
|||Hi again,
I did some other tests with english this time, and they all seemed
successful...until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif
Why is the CONTAINS returning no row at all? ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?
What's the problem with full text indexing? :S
And yes I did repopulate all the indexes before running this query...
help would be greatly appreciated!
thanks
ibiza

Full text indexing with Japanese characters problem

Hi all,
I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.
As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?
Thanks!
ibiza
This is probably due to the way the Japanese characters are broken by the
word breakers at index time and stored in the full text index. For example
Japanese consists of 5 different character sets. Japanese "words" are
largely syllables, so when you search on a "word" what it matches to are a
variety of sub tokens/syllables.
What you need to do is have someone who is fluent in Japanese verify that
your search application is actually finding what you are looking for. My
knowledge of Japanese is limited to a few phrases.
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
"ibiza" <lambertb@.gmail.com> wrote in message
news:1143825609.907248.76110@.t31g2000cwb.googlegro ups.com...
> Hi all,
> I am quite experimented with SQL Server, but not that much with full
> text indexing. After some successful attempts with english fields, I've
> decided to try it with Japanese characters. I don't know why, but it
> seems to have a strange behaviour.
>
> As in this screenshot
> (http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
> function does not seem to return only fields with an exact word match
> of the given "word" (query), but also strange results which does not
> even correspond to the query. Can anybody help me with that one?
>
> Thanks!
>
> ibiza
>
|||Hi,
thanks a lot for your reply
Well, even if I am not "fluent" in Japanese, I can still read japanese
"hiragana" characters, that is, what my query is about.
Each symbol can be translated as a syllable, so =E3=81=8A=E3=82=82=E3=81=97=
=E3=82=8D (the
query) would be "omoshiro", "o-mo-shi-ro".
The three first rows are translated as "omoni", "omo" and "omoi". So
I'm wondering why these results are showing, because they only partly
contains the query word (the =E3=81=8A=E3=82=82 ("omo") part)
As far as I know Japanese and full text indexing, the query should only
return results where the full and comple quey is found (=E3=81=8A=E3=82=82=
=E3=81=97=E3=82=8D),
as I did a CONTAINS search with a whole word.
Can you help me a little more with that information?
thanks again
ibiza
|||This could be by design. For example in German a word like wanderlust is
broken as wardern, lust and wanderlist. So if you were to do a contains
query for wanderlust you would get hits to any of these words. It looks like
it is doing something similar.
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
"ibiza" <lambertb@.gmail.com> wrote in message
news:1143922571.663177.238920@.i39g2000cwa.googlegr oups.com...
Hi,
thanks a lot for your reply
Well, even if I am not "fluent" in Japanese, I can still read japanese
"hiragana" characters, that is, what my query is about.
Each symbol can be translated as a syllable, so ? (the
query) would be "omoshiro", "o-mo-shi-ro".
The three first rows are translated as "omoni", "omo" and "omoi". So
I'm wondering why these results are showing, because they only partly
contains the query word (the ? ("omo") part)
As far as I know Japanese and full text indexing, the query should only
return results where the full and comple quey is found (?),
as I did a CONTAINS search with a whole word.
Can you help me a little more with that information?
thanks again
ibiza
|||Hi there,
thanks again for your reply.
I did some other tests with english and they all seemed successful
until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif
Why is the CONTAINS returning no row at all? ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?
What's the problem with full text indexing? :S
And yes I did repopulate all the indexes before running this query...
thanks for your help!
ibiza

Full text indexing with Japanese characters problem

Hi all,
I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.
As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?
Thanks!
ibizaI'm not sure about your problem, but could I have a copy of your DB?
That looks amazingly interesting I love japanese ^_^|||well, if you can help me with that problem, I'd be glad to consider it

(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=
nce I
can find...
please help!
thanks
ibiza|||well, if you can help me with that problem, I'd be glad to consider it

(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=
nce I
can find...
someone please help!
thanks
ibiza|||Does text indexing support non-latin characters?|||Hi again,
I did some other tests with english this time, and they all seemed
successful...until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif
Why is the CONTAINS returning no row at all' ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?
What's the problem with full text indexing? :S
And yes I did repopulate all the indexes before running this query...
help would be greatly appreciated!
thanks
ibiza

Full text indexing with Japanese characters problem

Hi all,
I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.
As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?
Thanks! :)
ibizaI'm not sure about your problem, but could I have a copy of your DB?
That looks amazingly interesting :) I love japanese ^_^|||well, if you can help me with that problem, I'd be glad to consider it
:)
(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=nce I
can find...
please help!
thanks :)
ibiza|||well, if you can help me with that problem, I'd be glad to consider it
:)
(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=nce I
can find...
someone please help!
thanks :)
ibiza|||Does text indexing support non-latin characters?|||Hi again,
I did some other tests with english this time, and they all seemed
successful...until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif
Why is the CONTAINS returning no row at all' ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?
What's the problem with full text indexing? :S
And yes I did repopulate all the indexes before running this query...
help would be greatly appreciated!
thanks
ibiza

Full text indexing with Japanese characters problem

Hi all,
I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.
As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?
Thanks! :)
ibizaHi again,
I did some other tests with english this time, and they all seemed
successful...until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif
Why is the CONTAINS returning no row at all' ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?
What's the problem with full text indexing? :S
And yes I did repopulate all the indexes before running this query...
help would be greatly appreciated!
thanks
ibiza

Full text indexing with Japanese characters problem

Hi all,

I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.

As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?

Thanks! :)

ibizaI'm not sure about your problem, but could I have a copy of your DB?
That looks amazingly interesting :) I love japanese ^_^|||well, if you can help me with that problem, I'd be glad to consider it
:)
(be warned though, it's more than 500MB in size...)

But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "おも", but that's the only resemblance I
can find...

please help!

thanks :)
ibiza|||well, if you can help me with that problem, I'd be glad to consider it
:)
(be warned though, it's more than 500MB in size...)

But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "おも", but that's the only resemblance I
can find...

someone please help!

thanks :)
ibiza|||Does text indexing support non-latin characters?|||Hi again,

I did some other tests with english this time, and they all seemed
successful...until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif

Why is the CONTAINS returning no row at all?? ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?

What's the problem with full text indexing? :S

And yes I did repopulate all the indexes before running this query...

help would be greatly appreciated!
thanks

ibiza

full text indexing feature of SQL Server .

I am using the full text indexing feature of SQL Server .

I have indexed two text fields in the Content table and two other text fields in the ContentDetail table

The select queries are shown below for the two seperate calls.

I want to be able to show the results based on the rank ( The column that has the best match for the searched key ).

Since I am getting two different RANK arrays from the two calls, can i resort them in the code to get the new rank order to show the results in the screen

SELECT KEY_TBL.RANK,FT_TBL.Description, FT_TBL.Title
FROM Content AS FT_TBL
INNER JOIN
CONTAINSTABLE( Content, * ,
'ISABOUT (Cancer weight(1),Survivor weight(1), Shared weight(1), Story weight(1))' ) AS KEY_TBL
ON FT_TBL.ContentID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC

SELECT KEY_TBL.RANK,FT_TBL.Description, FT_TBL.SubTitle
FROM ContentDetail AS FT_TBL
INNER JOIN
CONTAINSTABLE( ContentDetail, * ,
'ISABOUT (Cancer weight(1),Survivor weight(1), Shared weight(1), Story weight(1))' ) AS KEY_TBL
ON FT_TBL.ContentDetailID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESCThat would make a good pole...

How many dba's use this feature...

Never have...from what I understand it's a pig...|||I tried to get Full Text Search to work for a little while, until I realized you can not get "historical" to show up for searches with a search predicate of "history". I also had to make the queries in dynamic SQL, since you have so many dang quotes in the query. Pain in the neck, really.

fts within a single record

I am hoping that someone has an "it's obvious" solution. I have a bunch (~100) of text fields in a single record. I want a user to be able to search all of the fields (within this single record) for a string, and to return the field number(s) of where the string was found. Is there a way to do this within the MSSQL server's built in fts catalog, (or some other product) or do I need to code this by hand? I could limit the searchable strings to a finite set, if needed.I believe that you can do this with Full Text Search. You must, however, have a finite set of columns to search upon. You must also have a primary key for the Full Text Search engine to be able to identify the specific record with matching search criteria.

You must enable install and enable Full Text Search (it is not installed by default). You must then create the Full Text Search process (I cheat like crazy and use the wizard in EM; this is NOT recommended). It is recommended that you use QA and create the processes yourself.

You will need to populate the Full Text catalogue initially and then create a schedule to update the Full Text catalogue periodically.

Regards,

Hugh Scott

Originally posted by wooliewillie
I am hoping that someone has an "it's obvious" solution. I have a bunch (~100) of text fields in a single record. I want a user to be able to search all of the fields (within this single record) for a string, and to return the field number(s) of where the string was found. Is there a way to do this within the MSSQL server's built in fts catalog, (or some other product) or do I need to code this by hand? I could limit the searchable strings to a finite set, if needed.