 |
|
|
|
Next: can't seem to find the file
|
| Author |
Message |
External
Since: Nov 06, 2008
Posts: 6
|
(Msg. 1) Posted: Thu Nov 06, 2008 1:01 pm
Post subject: Contains Clause Issue - SQL 2000
Archived from groups: microsoft>public>sqlserver>fulltext (more info?)
|
|
|
The Full-Index Catalog is working fine and repopulating as it should. But the
query is not returning the expected results:
This is the string in the TicketNotes_Text column that in catalog is base on:
test00998877665544332211
If I run this I get the appropriate result back:
SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
DMS_TICKETNOTES
WHERE Contains(TicketNote_Text, '"*Test*" ')
But if I run this I return a empty recordset, I get records back but not my
test record:
SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
DMS_TICKETNOTES
WHERE Contains(TicketNote_Text, '"*0099*" ')
If you have any clues, I would be grateful.
--
Thanks,
Art
Database Administrator
Dealer Marketing Services, Inc..
arthurlorenzini.TakeThisOut@promaxonline.com
309.314.2176 |
|
| Back to top |
|
 | |
External
Since: Jan 10, 2008
Posts: 608
|
(Msg. 2) Posted: Thu Nov 06, 2008 4:19 pm
Post subject: Re: Contains Clause Issue - SQL 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Art,
Here is a 2005 link, but it is the same in 2000 and 2008.
http://technet.microsoft.com/en-us/library/ms187787(SQL.90).aspx
Full-text search only supports "the prefix of a word or phrase" not post-fix
or in-fix. Therefore, it will only process the * at the end of a string.
So,
*Test* = Test*
*0099* = 0099*
If you need to search for 0099, you will need to combine full-text search
with a wildcard search. E.g.
TextColumn LIKE '%0099%'
RLF
"dbalorenzini" wrote in message
> The Full-Index Catalog is working fine and repopulating as it should. But
> the
> query is not returning the expected results:
>
> This is the string in the TicketNotes_Text column that in catalog is base
> on:
>
> test00998877665544332211
>
> If I run this I get the appropriate result back:
>
> SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
> DMS_TICKETNOTES
> WHERE Contains(TicketNote_Text, '"*Test*" ')
>
> But if I run this I return a empty recordset, I get records back but not
> my
> test record:
>
> SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
> DMS_TICKETNOTES
> WHERE Contains(TicketNote_Text, '"*0099*" ')
>
> If you have any clues, I would be grateful.
>
> --
> Thanks,
> Art
> Database Administrator
> Dealer Marketing Services, Inc..
> arthurlorenzini DeleteThis @promaxonline.com
> 309.314.2176 |
|
| Back to top |
|
| |
External
Since: Nov 06, 2008
Posts: 6
|
(Msg. 3) Posted: Thu Nov 06, 2008 4:19 pm
Post subject: Re: Contains Clause Issue - SQL 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
So I took yur suggestion and tried the following statement:
SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
DMS_TICKETNOTES
WHERE Contains(TicketNote_Text, ' %0099% ')
But this returned nothing.
Again the string is actually test00998877665544332211
--
Thanks,
Art
Database Administrator
Dealer Marketing Services, Inc..
arthurlorenzini RemoveThis @promaxonline.com
309.314.2176
"Russell Fields" wrote:
> Art,
>
> Here is a 2005 link, but it is the same in 2000 and 2008.
> http://technet.microsoft.com/en-us/library/ms187787(SQL.90).aspx
>
> Full-text search only supports "the prefix of a word or phrase" not post-fix
> or in-fix. Therefore, it will only process the * at the end of a string.
> So,
>
> *Test* = Test*
> *0099* = 0099*
>
> If you need to search for 0099, you will need to combine full-text search
> with a wildcard search. E.g.
> TextColumn LIKE '%0099%'
>
> RLF
>
> "dbalorenzini" wrote in message
>
> > The Full-Index Catalog is working fine and repopulating as it should. But
> > the
> > query is not returning the expected results:
> >
> > This is the string in the TicketNotes_Text column that in catalog is base
> > on:
> >
> > test00998877665544332211
> >
> > If I run this I get the appropriate result back:
> >
> > SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
> > DMS_TICKETNOTES
> > WHERE Contains(TicketNote_Text, '"*Test*" ')
> >
> > But if I run this I return a empty recordset, I get records back but not
> > my
> > test record:
> >
> > SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
> > DMS_TICKETNOTES
> > WHERE Contains(TicketNote_Text, '"*0099*" ')
> >
> > If you have any clues, I would be grateful.
> >
> > --
> > Thanks,
> > Art
> > Database Administrator
> > Dealer Marketing Services, Inc..
> > arthurlorenzini RemoveThis @promaxonline.com
> > 309.314.2176
>
>
> >> Stay informed about: Contains Clause Issue - SQL 2000 |
|
| Back to top |
|
| |
External
Since: Jan 10, 2008
Posts: 608
|
(Msg. 4) Posted: Thu Nov 06, 2008 4:59 pm
Post subject: Re: Contains Clause Issue - SQL 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Art,
Sorry, but I was apparently not clear. My example did not use CONTAINS. It
was more like this:
SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text
FROM DMS_TICKETNOTES
WHERE TicketNote_Text LIKE ' %0099% '
In this case the CONTAINS is meaningless since you have no data to match.
But you could be searching complimentary data such as below where fulltext
index and string search combine to get your answer:
SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text
FROM DMS_TICKETNOTES
WHERE CONTAINS (TicketNote_Text, 'Johns* ')
AND TicketNote_Text LIKE ' %0099% '
So, my point was where a particular wildcard could be applied.
RLF
"dbalorenzini" wrote in message
> So I took yur suggestion and tried the following statement:
>
> SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
> DMS_TICKETNOTES
> WHERE Contains(TicketNote_Text, ' %0099% ')
>
> But this returned nothing.
>
> Again the string is actually test00998877665544332211
>
> --
> Thanks,
> Art
> Database Administrator
> Dealer Marketing Services, Inc..
> arthurlorenzini RemoveThis @promaxonline.com
> 309.314.2176
>
>
> "Russell Fields" wrote:
>
>> Art,
>>
>> Here is a 2005 link, but it is the same in 2000 and 2008.
>> http://technet.microsoft.com/en-us/library/ms187787(SQL.90).aspx
>>
>> Full-text search only supports "the prefix of a word or phrase" not
>> post-fix
>> or in-fix. Therefore, it will only process the * at the end of a
>> string.
>> So,
>>
>> *Test* = Test*
>> *0099* = 0099*
>>
>> If you need to search for 0099, you will need to combine full-text search
>> with a wildcard search. E.g.
>> TextColumn LIKE '%0099%'
>>
>> RLF
>>
>> "dbalorenzini" wrote in message
>>
>> > The Full-Index Catalog is working fine and repopulating as it should.
>> > But
>> > the
>> > query is not returning the expected results:
>> >
>> > This is the string in the TicketNotes_Text column that in catalog is
>> > base
>> > on:
>> >
>> > test00998877665544332211
>> >
>> > If I run this I get the appropriate result back:
>> >
>> > SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
>> > DMS_TICKETNOTES
>> > WHERE Contains(TicketNote_Text, '"*Test*" ')
>> >
>> > But if I run this I return a empty recordset, I get records back but
>> > not
>> > my
>> > test record:
>> >
>> > SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
>> > DMS_TICKETNOTES
>> > WHERE Contains(TicketNote_Text, '"*0099*" ')
>> >
>> > If you have any clues, I would be grateful.
>> >
>> > --
>> > Thanks,
>> > Art
>> > Database Administrator
>> > Dealer Marketing Services, Inc..
>> > arthurlorenzini RemoveThis @promaxonline.com
>> > 309.314.2176
>>
>>
>> >> Stay informed about: Contains Clause Issue - SQL 2000 |
|
| Back to top |
|
| |
| Related Topics: | sql 2000 sp4 image iflter issue - I have table with an image column that stores word documents. I added a cloumn called "DocumentType" and poulate it with .doc. I used the wizard to setup an FT index. I run a select staement like this one: SELECT document_name FROM Documents ...
Column in contains clause? - Dear all, Another question: I would like to get an overview of how often a number of words, stored in a table KO, occur in a full text indexed column on another table. I can do this seperately: select * from names id | name ---+------ 1 | bush 2 |..
Is the Contains clause case sensitive? - Hi all... Someone testing the Search program I am putting together brought this to my attention today. The Contains clause is: contains((List_Name1),'("furniture" & "decor") and ("home" | "accessories") &...
funny search results with 'contains' clause - hi, it is really funny how ft search works: it seems to depend highly on what could be indexed, but it still doesn't follow a determined order. consider the following queries: select name from person where contains(name, 'hammer') select name from..
Full Text With Ranking Issue - I am using SQl server 2005 64 Bit Service Pack 2 with Full text on one table and only on one field. When I run query on my table of 800,000 rows Full text search find 4830 records within 18 seconds with skill Oracle in State CA. SELECT * FROM.. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
