Full Text Search - Conatins doesnot work

Our SQL Server 2000 (sp1) on a Windows 2000 system is configured for a full text search.

The ntext column having a full text index has a value as follows:

Rob Proctor's Tips: Creating a "Tropical" Get-Away At Home

when I search it by

SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical Get Away"')

it displays the result as

NTEXT_COL
======================================
Rob Proctor's Tips: Creating a "Tropical" Get-Away At Home

But the query

SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical Get Aw*"')

do not return any rows..

Can anybody give me the reason for this behaviour, or is it a known bug?

Thanks

Sajan'Get' is included in Noise-word file and therefore query does not return what do you expect. Just remove hyphen from Get-Away (GetAway), repopulate index, run query and you'll see.

SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical GetAw*"')

I agree it is stupid but I guess it was designed this way. You could update list of your Noise words but size of indexes will be increased...

Full join problem...

Hi,
My scenario is that i do a full join for check after duplicates name with
the difference function.
The problem is that i want to set a group value for each probably
duplicates.
After that i can set a distinct or something.
Help.
Here are the result i want to get help with (group value column):
name,id,name,id,diff_value,group_value
Smyth 2 Smythe 1 4 ?
Smith 3 Smythe 1 4 ?
Smythe 1 Smyth 2 4 ?
Smith 3 Smyth 2 4 ?
Smythe 1 Smith 3 4 ?
Smyth 2 Smith 3 4 ?
Anderson 5 Andersson 4 4 ?
Anderzon 6 Andersson 4 4 ?
Andersson 4 Anderson 5 4 ?
Anderzon 6 Anderson 5 4 ?
Andersson 4 Anderzon 6 4 ?
Anderson 5 Anderzon 6 4 ?
For all Smith spelling a want to set a group_value 1,
for anderson group_value 2.
And so on.
Here are the ddl example.
Create Table table1
(
ID Integer Identity(1,1),
name varchar(50)
)
Insert Into table1 (name)
Values ('Smythe')
Insert Into table1 (name)
Values ('Smyth')
Insert Into table1 (name)
Values ('Smith')
Insert Into table1 (name)
Values ('Andersson')
Insert Into table1 (name)
Values ('Anderson')
Insert Into table1 (name)
Values ('Anderzon')
And the query:
select a.name,a.id,b.name,b.id,DIFFERENCE(a.name,b.name) as diff_value,'?'
as group_value from
table1 as a,table1 as b
where DIFFERENCE(a.name,b.name)>2 and a.id<>b.id
Thanx
// twHi
I am not sure why you are want all (a,b) and (b,a) tuples as they are
effectively duplicates?
If not then maybe you can use a.id as the grouping value
e.g.
select a.name,a.id,b.name,b.id,DIFFERENCE(a.name,b.name) as diff_value,a.id
as group_value
from table1 as a
left join table1 as b ON a.id<b.id
WHERE DIFFERENCE(a.name,b.name)>2
John
"tw" <tw@.tactics.se> wrote in message
news:OMkWVRSlFHA.3828@.TK2MSFTNGP12.phx.gbl...
> Hi,
> My scenario is that i do a full join for check after duplicates name with
> the difference function.
> The problem is that i want to set a group value for each probably
> duplicates.
> After that i can set a distinct or something.
> Help.
> Here are the result i want to get help with (group value column):
> name,id,name,id,diff_value,group_value
> Smyth 2 Smythe 1 4 ?
> Smith 3 Smythe 1 4 ?
> Smythe 1 Smyth 2 4 ?
> Smith 3 Smyth 2 4 ?
> Smythe 1 Smith 3 4 ?
> Smyth 2 Smith 3 4 ?
> Anderson 5 Andersson 4 4 ?
> Anderzon 6 Andersson 4 4 ?
> Andersson 4 Anderson 5 4 ?
> Anderzon 6 Anderson 5 4 ?
> Andersson 4 Anderzon 6 4 ?
> Anderson 5 Anderzon 6 4 ?
> For all Smith spelling a want to set a group_value 1,
> for anderson group_value 2.
> And so on.
> Here are the ddl example.
> Create Table table1
> (
> ID Integer Identity(1,1),
> name varchar(50)
> )
> Insert Into table1 (name)
> Values ('Smythe')
> Insert Into table1 (name)
> Values ('Smyth')
> Insert Into table1 (name)
> Values ('Smith')
> Insert Into table1 (name)
> Values ('Andersson')
> Insert Into table1 (name)
> Values ('Anderson')
> Insert Into table1 (name)
> Values ('Anderzon')
>
> And the query:
> select a.name,a.id,b.name,b.id,DIFFERENCE(a.name,b.name) as diff_value,'?'
> as group_value from
> table1 as a,table1 as b
> where DIFFERENCE(a.name,b.name)>2 and a.id<>b.id
> Thanx
> // tw
>|||Thanx for the help.
But i want all "Smythe" spellings in the same group,
and all "Andersson" in the same.
Is that possible?
Now is the group value 1 and 2 for "Smith" and 4 and 5 for "Andersson".
// tw
"John Bell" <jbellnewsposts@.hotmail.com> skrev i meddelandet
news:OOVutGTlFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi
> I am not sure why you are want all (a,b) and (b,a) tuples as they are
> effectively duplicates?
> If not then maybe you can use a.id as the grouping value
> e.g.
> select a.name,a.id,b.name,b.id,DIFFERENCE(a.name,b.name) as
> diff_value,a.id as group_value
> from table1 as a
> left join table1 as b ON a.id<b.id
> WHERE DIFFERENCE(a.name,b.name)>2
> John
> "tw" <tw@.tactics.se> wrote in message
> news:OMkWVRSlFHA.3828@.TK2MSFTNGP12.phx.gbl...
>|||Hi
This may have already be posted but I can't be certain!
The group value for Smythe is 1
The group value for Smyth is 2
The group value for Andersson is 4
The group value for Anderson is 5
If you wish to remove the child group values then you could do
something like:
SELECT P.ParentName, P.parent_id, P.Childname, P.Child_id,
P.diff_value, P.GroupValue
FROM
( SELECT a.name as ParentName,a.id as parent_id,b.name as Childname,
b.id as Child_id,DIFFERENCE(a.name,b.name) as diff_value, A.id AS
GroupValue
from table1 as a
left join table1 as b ON a.id<b.id
WHERE DIFFERENCE(a.name,b.name)>2 ) P
WHERE P.parent_id NOT IN ( SELECT b.id FROM table1 as a
left join table1 as b ON a.id<b.id
WHERE DIFFERENCE(a.name,b.name)>2 )
But the ParentName will depend on the order of the ids.
John

FTS Q - Proximate meaning of phrases

Hi
Is it possible to find records that contain the string "cyber-shot" when the value for search is "cybershot"?? (This is an example and I need a dynamic solution)
Thanks,
Inon.Probably (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_3rqg.asp).

-PatP|||Depends upon exactly what you want. Are you doing a fuzzy search, or do you want to find all the values that contain the same characters? Do the characters have to be in the same order? Do you just want to ignore non-alphanumeric characters?
You will have to give more details on the problem if you want more details on the answer.

FTS index updating

Hello,
Is it possible to know status of FTS index on value in some column of
row, I mean, how can I know FTS index have been updated already or haven't
? For example, value of some row has been changed, but FTS index hasn't
been changed yet. I need to know when after value updating I can run FTS
queries. Does any body know some way to do this, and is it possible or not?
Thank you
There are many ways of doing this - however, they are all complex.
If you are using change tracking you could poll sysfulltextnotify, and key
off the ftkey column which corresponds to your primary key.
You could also enable logging and poll the log looking for the pk value. To
do this go to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\G ather\SQLServer\SQL0000500
005 (using the SQL 0000X0000X which corresponds to your catalog), and set
LogDisabled to 1.
Then there is the code sample which accompanies this article which can be
used to trap for a specific row.
http://msdn.microsoft.com/library/de...tml/sp04f9.asp
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
<denis_rusakov@.epam.com> wrote in message
news:epbCZ%23mnFHA.3036@.TK2MSFTNGP14.phx.gbl...
> Hello,
> Is it possible to know status of FTS index on value in some column of
> row, I mean, how can I know FTS index have been updated already or haven't
> ? For example, value of some row has been changed, but FTS index hasn't
> been changed yet. I need to know when after value updating I can run FTS
> queries. Does any body know some way to do this, and is it possible or
not?
> Thank you
>
>
|||Thank you very much
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23pyvuPonFHA.572@.TK2MSFTNGP15.phx.gbl...
> There are many ways of doing this - however, they are all complex.
> If you are using change tracking you could poll sysfulltextnotify, and key
> off the ftkey column which corresponds to your primary key.
> You could also enable logging and poll the log looking for the pk value.
> To
> do this go to
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\G ather\SQLServer\SQL0000500
> 005 (using the SQL 0000X0000X which corresponds to your catalog), and set
> LogDisabled to 1.
> Then there is the code sample which accompanies this article which can be
> used to trap for a specific row.
> http://msdn.microsoft.com/library/de...tml/sp04f9.asp
> --
> 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
> <denis_rusakov@.epam.com> wrote in message
> news:epbCZ%23mnFHA.3036@.TK2MSFTNGP14.phx.gbl...
> not?
>