Full text search can not return result

Hi all,
In my case, I build a full text search query. like "select * from _tt_fields where contains(string,'"development group"')", when I click F5 then execute the sql, it returned the records, but when I click execute the query more than 4 times, it does not return anything!
From http://www.developmentnow.com/g/104_0_0_0_0_0/sql-server-fulltext.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Very rarely when you are in the middle of a merge you may get an
inaccurate count like what you are seeing. it lasts for milli-
seconds.
On Jan 16, 4:56 am, ken<nos...@.developmentnow.com> wrote:
> Hi all,
> In my case, I build a full text search query. like "select * from _tt_fields where contains(string,'"development group"')", when I click F5 then execute the sql, it returned the records, but when I click execute the query more than 4 times, it does not return anything!
> Fromhttp://www.developmentnow.com/g/104_0_0_0_0_0/sql-server-fulltext.htm
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com

Full text search alternative without leading wildcard limitation

As has been mentioned several times in this group, SQL Server does not
support a leading wildcard in full text searches (i.e., CONTAINS(field,
'*tion')).
To get around this I have been looking at external full text search
engines that might not have this restriction but with little success:
most of the others seem to have the same restriction (e.g., Lucene,
swish-e, etc.). The one that did not was Namazu but it was slow.
So my question is: has anyone found or come up with a full text search
application that does not have this wildcard limitation and is fast
(faster than simply using LIKE)?
google@.macrotex.net wrote on 24 Mar 2006 06:32:06 -0800:

> As has been mentioned several times in this group, SQL Server does not
> support a leading wildcard in full text searches (i.e., CONTAINS(field,
> '*tion')).
> To get around this I have been looking at external full text search
> engines that might not have this restriction but with little success:
> most of the others seem to have the same restriction (e.g., Lucene,
> swish-e, etc.). The one that did not was Namazu but it was slow.
> So my question is: has anyone found or come up with a full text search
> application that does not have this wildcard limitation and is fast
> (faster than simply using LIKE)?
You could, if you have the space in your database, create a copy of the
columns you wish to search in this way in reverse, and use FTS to index
this. eg. add a column called fieldreverse, and do
UPDATE table SET fieldreverse = REVERSE(field)
then index that, and search it using
CONTAINS(field, 'noit*'))
You could automatic the creation of the reversed data using a trigger on the
table for the normal column. Not a pretty solution, but it'll work.
Dan

Full text newbie...

Hi, group,
Made these days some tests and not understand very clear how contains or
containstable perform the search.
The catalog was built on 2...5 columns, various scenarios.
I tried with 2 words I am sure they exists in 2 different fields, but
contains nor containstable does not return those records. I tried both
"Johny Walker" and "Johny" AND "Walker" and no succes.
Is there any syntax allowing me to find "Johny" in a field AND "Walker" in
other field, in the same record, both fields included in FT catalog?
Thank you,
Renato
Not really. Both words must be in the same column with a Contains search, a
FreeText search can look across 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
"Renato Aranghelovici" <renatoa@.rdslink.ro> wrote in message
news:OYRS%234SIGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Hi, group,
> Made these days some tests and not understand very clear how contains or
> containstable perform the search.
> The catalog was built on 2...5 columns, various scenarios.
> I tried with 2 words I am sure they exists in 2 different fields, but
> contains nor containstable does not return those records. I tried both
> "Johny Walker" and "Johny" AND "Walker" and no succes.
> Is there any syntax allowing me to find "Johny" in a field AND "Walker" in
> other field, in the same record, both fields included in FT catalog?
> Thank you,
> Renato
>
|||Ok, thank you.
This responded only partially to my initial request: if I want to find all
records that contains "Johny" in any indexed field AND "Walker" in other
indexed field ( or both in the same field) is this possible with free text
search ? And how?
Thank you,
Renato
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u1CeLYVIGHA.3348@.tk2msftngp13.phx.gbl...
> Not really. Both words must be in the same column with a Contains search,
> a FreeText search can look across 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
> "Renato Aranghelovici" <renatoa@.rdslink.ro> wrote in message
> news:OYRS%234SIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>

Full Text in Sql Server 2005

Hello,
Aplogies ifg this is the worng news group for this question.
I have been looking in detail at the full text catlog in Sql server 2000. I
have found some of the facilaities very useful but a very big disavantage
for me is the wildcard seacrhing. You can only search ast the end of a word
or phrase, i.e.
sail*
sail*oat doies not work and neither does *sail
Does anyone know if these features will be in the 2005 version of SQL server
Kind Regards
Philip
no its still only at the end.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Philip Poole" <philip@.dontreply.org> wrote in message
news:414185ad$0$22762$db0fefd9@.news.zen.co.uk...
> Hello,
> Aplogies ifg this is the worng news group for this question.
> I have been looking in detail at the full text catlog in Sql server 2000.
I
> have found some of the facilaities very useful but a very big disavantage
> for me is the wildcard seacrhing. You can only search ast the end of a
word
> or phrase, i.e.
> sail*
> sail*oat doies not work and neither does *sail
> Does anyone know if these features will be in the 2005 version of SQL
server
> Kind Regards
> Philip
>
|||Hello Hillary,
Thankyou for ht einformaion,
do you know uf they are planning on adding to this feature in the future?
Phil
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O2YdYQzlEHA.2764@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> no its still only at the end.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Philip Poole" <philip@.dontreply.org> wrote in message
> news:414185ad$0$22762$db0fefd9@.news.zen.co.uk...
2000.[vbcol=seagreen]
> I
disavantage
> word
> server
>
|||don't have a clue. IBM's equivalent of SQL FTS does have it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Philip Poole" <philip@.dontreply.org> wrote in message
news:4141c6a9$0$22759$db0fefd9@.news.zen.co.uk...[vbcol=seagreen]
> Hello Hillary,
> Thankyou for ht einformaion,
> do you know uf they are planning on adding to this feature in the future?
> Phil
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O2YdYQzlEHA.2764@.TK2MSFTNGP11.phx.gbl...
> 2000.
> disavantage
a
>
|||BTW - when you have feature requests like this send them to
sqlwish@.microsoft.com
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Philip Poole" <philip@.dontreply.org> wrote in message
news:4141c6a9$0$22759$db0fefd9@.news.zen.co.uk...[vbcol=seagreen]
> Hello Hillary,
> Thankyou for ht einformaion,
> do you know uf they are planning on adding to this feature in the future?
> Phil
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O2YdYQzlEHA.2764@.TK2MSFTNGP11.phx.gbl...
> 2000.
> disavantage
a
>
|||Philip,
I doubt very much if Microsoft is planning on adding this functionality as
it is dependent upon the SQL Server 2005 MSFTESQL search engine and Beta2 is
feature complete. If you want I can forward this request to my contacts at
Microsoft.
Thanks,
John
"Philip Poole" <philip@.dontreply.org> wrote in message
news:4141c6a9$0$22759$db0fefd9@.news.zen.co.uk...[vbcol=seagreen]
> Hello Hillary,
> Thankyou for ht einformaion,
> do you know uf they are planning on adding to this feature in the future?
> Phil
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O2YdYQzlEHA.2764@.TK2MSFTNGP11.phx.gbl...
> 2000.
> disavantage
a
>
|||Hello John,
That would be absolutly fantastic thanks.
Take care of yourself
Philip
"John Kane" <jt-kane@.comcast.net> wrote in message
news:eGDGMebmEHA.3452@.TK2MSFTNGP15.phx.gbl...
> Philip,
> I doubt very much if Microsoft is planning on adding this functionality as
> it is dependent upon the SQL Server 2005 MSFTESQL search engine and Beta2
is[vbcol=seagreen]
> feature complete. If you want I can forward this request to my contacts at
> Microsoft.
> Thanks,
> John
>
> "Philip Poole" <philip@.dontreply.org> wrote in message
> news:4141c6a9$0$22759$db0fefd9@.news.zen.co.uk...
future?[vbcol=seagreen]
of[vbcol=seagreen]
> a
SQL
>
|||Don't forget that you can request the feature yourself, just as well as John
can. :-) Simply send your request to sqlwish@.microsoft.com. It both goes
directly to the inbox of the folks who make the product decisions and it
also gets logged into an internal database so we can keep track of how many
folks are asking for similar things. You may not get a personalized
response, but you will be heard.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Philip Poole" <philip@.dontreply.org> wrote in message
news:4146a884$0$22762$db0fefd9@.news.zen.co.uk...
> Hello John,
> That would be absolutly fantastic thanks.
> Take care of yourself
> Philip
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:eGDGMebmEHA.3452@.TK2MSFTNGP15.phx.gbl...
> is
> future?
> of
> SQL
>
|||Thanks, Stephen,
I had forgotten about that! Although, I am having lunch with someone in the
SQL Dev group soon and I was going to discuss this subject at that time
along with other related FTS subjects.
Philip, it would be best if you emailed sqlwish@.microsoft.com directly.
Regards,
John
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:uCYSy$mmEHA.2680@.TK2MSFTNGP15.phx.gbl...
> Don't forget that you can request the feature yourself, just as well as
John
> can. :-) Simply send your request to sqlwish@.microsoft.com. It both goes
> directly to the inbox of the folks who make the product decisions and it
> also gets logged into an internal database so we can keep track of how
many
> folks are asking for similar things. You may not get a personalized
> response, but you will be heard.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Philip Poole" <philip@.dontreply.org> wrote in message
> news:4146a884$0$22762$db0fefd9@.news.zen.co.uk...
Beta2[vbcol=seagreen]
server[vbcol=seagreen]
end
>
|||For what it is worth we badly need the feature as well for a big prospect we
have. I'll send a message to sqlwish as well.
Regards
- Michele

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