& in FTS

I'm having a problem when "&" is in the search term.
For example, I have an article in my database that has "flat monitors &
Toshiba magic". When a user does a FTS using that search term (or, here's
some SQL: SELECT * FROM FREETEXTTABLE(forum_pacs,*,'"Flat Monitors & Toshiba
magic"') ORDER BY Rank Desc), an article with that exact match text does not
come up first. In fact, it is about 40 or 50 on a list of several hundred,
most with just a match on "monitor" or "Toshiba".
Anyone know why it doesn't come up first? Is there something special about
"&"?
Cheers,
Owen
The and is ignore in FreeText. You will get better results in Contains.
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
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:eq8pq0ozFHA.3660@.TK2MSFTNGP15.phx.gbl...
> I'm having a problem when "&" is in the search term.
> For example, I have an article in my database that has "flat monitors &
> Toshiba magic". When a user does a FTS using that search term (or, here's
> some SQL: SELECT * FROM FREETEXTTABLE(forum_pacs,*,'"Flat Monitors &
> Toshiba magic"') ORDER BY Rank Desc), an article with that exact match
> text does not come up first. In fact, it is about 40 or 50 on a list of
> several hundred, most with just a match on "monitor" or "Toshiba".
> Anyone know why it doesn't come up first? Is there something special about
> "&"?
> Cheers,
> Owen
>
|||Owen,
First of all, could you post the full output of -- SELECT @.@.version -- as
this is very helpful info when troubleshooting SQL FTS issues.
Secondly, I tested this using similar text using SQL Server 2000 on a
Win2003 server (see attached sql script file: FTS_punctuation.sql) and while
I do see more results with FREETEXT than CONTAINS, my testing on Win2003
demonstrates that CONTAINS will ignore punctuation characters between search
words in a search phrase (multiple words within double quotes), for example:
-- freetext
-- 12 rows, original query
SELECT * FROM FTSPunc where FREETEXT(*,'"Flat Monitors & Toshiba magic"')
-- 12 rows, with * (asterisk) or | (bar)
SELECT * FROM FTSPunc where FREETEXT(*,'"Flat Monitors | Toshiba magic"')
-- 12 rows, with OR
SELECT * FROM FTSPunc where FREETEXT(*,'"Flat Monitors or Toshiba magic"')
-- contains
-- 5 rows with contains, punctuation is ignored
SELECT * FROM FTSPunc where CONTAINS(*,'"Flat Monitors & Toshiba magic"')
-- rows with contains and any punctuation is ignored
SELECT * FROM FTSPunc where CONTAINS(*,'"Flat Monitors # Toshiba magic"')
-- 3 rows with contains, OR is ignored, OR in noise.enu
SELECT * FROM FTSPunc where CONTAINS(*,'"Flat Monitors or Toshiba magic"')
-- 9 rows, with and without "*" in between search words
SELECT * FROM FTSPunc where CONTAINS(*,'"Toshiba * magic"')
You may see different results, if you have SQL Server 2000 installed on
Win2K due to different OS-supplied word breakers.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:eq8pq0ozFHA.3660@.TK2MSFTNGP15.phx.gbl...
> I'm having a problem when "&" is in the search term.
> For example, I have an article in my database that has "flat monitors &
> Toshiba magic". When a user does a FTS using that search term (or, here's
> some SQL: SELECT * FROM FREETEXTTABLE(forum_pacs,*,'"Flat Monitors &
> Toshiba
> magic"') ORDER BY Rank Desc), an article with that exact match text does
> not
> come up first. In fact, it is about 40 or 50 on a list of several
> hundred,
> most with just a match on "monitor" or "Toshiba".
> Anyone know why it doesn't come up first? Is there something special about
> "&"?
> Cheers,
> Owen
>
begin 666 FTS_punctuation.sql
M#0HM+0E&:6QE;F%M93H@.1E137W!U;F-T=6%T:6]N+G-Q; T*+2TM"5!U<G!O
M<V4Z(%1O('1E<W0@.=&AE($94($EN9&5X:6YG(&]F('1E>'0@.=VET:" B<'5N
M8V%T:6]N(B!U<VEN9R!54U]%;F=L:7-H("AD969A=6QT*2X-"BTM"45N=FER
M;VUE;G0Z(%-13"!344P@.,C P,"!O;B!7:6XR,# S(%-E<G9E<B!34#$-"BTM
M"6UO9&EF:65D.B X.C0Y(%!-(#$P+S$Q+S(P,#4-"@.T*=7-E('!U8G,-"F=O
M#0IS96QE8W0@.0$!V97)S:6]N"2TM(%-13"!397)V97(@.(#(P,# @.+2 X+C P
M+C4S-" N+BX@.;VX@.16YT97)P<FES92!%9&ET:6]N(&]N(%=I;F1O=W,@.3E0@.
M-2XR("A"=6EL9" S-S$X.B I( T*<V5L96-T($! ;&%N9W5A9V4)+2T@.=7-?
M96YG;&ES: T*<V5L96-T($! <V5R=F5R;F%M90DM+2!*5$M.151%10T*9V\-
M"G-P7V9U;&QT97AT7W-E<G9I8V4@.)V-L96%N7W5P)PT*9V\-"G-P7V9U;&QT
M97AT7V1A=&%B87-E("=E;F%B;&4G("TM('5S92!/3DQ9($]N8V4@.(2$A#0IG
M;PT*:68@.97AI<W1S("AS96QE8W0@.*B!F<F]M('-Y<V]B:F5C=',@.=VAE<F4@.
M:60@./2!O8FIE8W1?:60H)T944U!U;F,G*2D-"B @.9')O<"!T86)L92!&5%-0
M=6YC#0IG;PT*0U)%051%(%1!0DQ%($944U!U;F,@.* T*("!+97E#;VP):6YT
M($E$14Y42519("@.Q+#$I($Y/5"!.54Q,( T*(" @.($-/3E-44D%)3E0@.1E13
M4'5N8U])1%@.@.4%))34%262!+15D@.0TQ54U1%4D5$+ T*("!497AT0V]L"71E
M>'0@.3E5,3"P-"B @.5F%R8VAA<D-O; EV87)C:&%R*#(U*2!.54Q,+ T*("!#
M:&%R0V]L"6-H87(H,S I($Y53$PL( T*("!4:6UE4W1A;7!#;VP)=&EM97-T
M86UP($Y53$P@.#0HI($].(%M04DE-05)970T*9V\-"@.T*=')U;F-A=&4@.=&%B
M;&4@.1E134'5N8PT*#0HM+2!);G-E<G0@.9&%T82XN+@.T*24Y315)4($944U!U
M;F,@.=F%L=65S*"=&;&%T($UO;FET;W)S("8@.5&]S:&EB82!M86=I8R<L("=%
M>'!O<G1E9"!F<F]M("!-87-T97)#;V]K)RP@.)TYE;W!O;&ET86X@.0VAI8VME
M;B<L($Y53$PI#0I)3E-%4E0@.1E134'5N8R!V86QU97,H)T9L870@.36]N:71O
M<G,@.?"!4;W-H:6)A(&UA9VEC)RP@.)T5X<&]R=&5D(&9R;VT@.($UA<W1E<D-O
M;VLG+" G3F5O<&]L:71A;B!#:&EC:V5N)RP@.3E5,3"D-"DE.4T525"!&5%-0
M=6YC('9A;'5E<R@.G1FQA="!-;VYI=&]R<R!A;F0@.5&]S:&EB82!M86=I8R<L
M("=%>'!O<G1E9"!F<F]M("!-87-T97)#;V]K)RP@.)TYE;W!O;&ET86X@.0VAI
M8VME;B<L($Y53$PI#0I)3E-%4E0@.1E134'5N8R!V86QU97,H)T9L870@.36]N
M:71O<G,@.;W(@.5&]S:&EB82!M86=I8R<L("=%>'!O<G1E9"!F<F]M("!-87-T
M97)#;V]K)RP@.)TYE;W!O;&ET86X@.0VAI8VME;B<L($Y53$PI#0I)3E-%4E0@.
M1E134'5N8R!V86QU97,H)T9L870@.36]N:71O<G,@.(R!4;W-H:6)A(&UA9VEC
M)RP@.)T5X<&]R=&5D(&9R;VT@.($UA<W1E<D-O;VLG+" G3F5O<&]L:71A;B!#
M:&EC:V5N)RP@.3E5,3"D-"DE.4T525"!&5%-0=6YC('9A;'5E<R@.G1FQA="!-
M;VYI=&]R<R E(%1O<VAI8F$@.;6%G:6,G+" G17AP;W)T960@.9G)O;2 @.36%S
M=&5R0V]O:R<L("=.96]P;VQI=&%N($-H:6-K96XG+"!.54Q,*0T*24Y315)4
M($944U!U;F,@.=F%L=65S*"=&;&%T($UO;FET;W)S("H@.5&]S:&EB82!M86=I
M8R<L("=%>'!O<G1E9"!F<F]M("!-87-T97)#;V]K)RP@.)TYE;W!O;&ET86X@.
M0VAI8VME;B<L($Y53$PI#0I)3E-%4E0@.1E134'5N8R!V86QU97,H)T9L870@.
M36]N:71O<G,@.<&QA8V5H;VQD97(@.5&]S:&EB82!M86=I8R<L("=%>'!O<G1E
M9"!F<F]M("!-87-T97)#;V]K)RP@.)TYE;W!O;&ET86X@.0VAI8VME;B<L($Y5
M3$PI#0I)3E-%4E0@.1E134'5N8R!V86QU97,H)V9L870@.;6]N:71O<G,G+" G
M8VQA<W-I8R!R96-I<&5S)RP@.)TYE;W!O;&ET86X@.0F5E9B<L($Y53$PI#0I)
M3E-%4E0@.1E134'5N8R!V86QU97,H)W1H:7,@.:7,@.=&AE('1H:7)D( ')O=R<L
M("=&4D]:14X@.3D5!4$],251!3B!43U)412<L("=.96%P;VQI=&%N(%1O<G1E
M)RP@.3E5,3"D-"DE.4T525"!&5%-0=6YC('9A;'5E<R@.G5&]S:&EB82!M86=I
M8R<L("=&4D]:14X@.3D5!4$],251!3B!43U)412<L("=.96%P;VQI=&%N(%1O
M<G1E)RP@.3E5,3"D-"DE.4T525"!&5%-0=6YC('9A;'5E<R@.G;6%G:6,G+" G
M1E)/6D5.($Y%05!/3$E404X@.5$]25$4G+" G3F5A<&]L:71A;B!4;W)T92<L
M($Y53$PI#0I)3E-%4E0@.1E134'5N8R!V86QU97,H)U1O<VAI8F$G+" G1E)/
M6D5.($Y%05!/3$E404X@.5$]25$4G+" G3F5A<&]L:71A;B!4;W)T92<L($Y5
M3$PI#0H-"F=O#0H-"@.T*+2T@.0W)E871E($94($EN9&5X#0IE>&5C('-P7V9U
M;&QT97AT7V-A=&%L;V<@.)T944U!C0V%T86QO9R<L)V-R96%T92<-"F5X96,@.
M<W!?9G5L;'1E>'1?=&%B;&4@.)T944U!U;F,G+"=C<F5A=&4G+ "=&5%-08T-A
M=&%L;V<G+"=&5%-0=6YC7TE$6"<-"F5X96,@.<W!?9G5L;'1E>'1?8V]L=6UN
M("=&5%-0=6YC)RPG5F%R8VAA<D-O;"<L)V%D9"<-"F5X96,@.<W!?9G5L;'1E
M>'1?8V]L=6UN("=&5%-0=6YC)RPG5&5X=$-O;"<L)V%D9"<@.+2T@.+# @.+2T@.
M;F5U=')A;"!W;W)D8G)E86ME<@.T*97AE8R!S<%]F=6QL=&5X=%]C;VQU;6X@.
M)T944U!U;F,G+"=#:&%R0V]L)RPG861D)R M+2 L," M+2!N975T<F%L('=O
M<F1B<F5A:V5R#0IE>&5C('-P7V9U;&QT97AT7W1A8FQE("=&5%-0=6YC)RP@.
M)V%C=&EV871E)PT*9V\-"@.T*+2T@.4W1A<G0@.1E0@.26YD97AI;F<N+BX-"F5X
M96,@.<W!?9G5L;'1E>'1?8V%T86QO9R G1E134&-#871A;&]G)RPG<W1A<G1?
M9G5L;"<-"F=O#0HM+2!786ET(&9O<B!&5"!);F1E>&EN9R!T;R!C;VUP;& 5T
M92!A;F0@.8VAE8VL@.3E0O5VEN,DL@.07!P;&EC871I;VX@.;&]G(&9O<B!S=6-C
M97-S+V5R<F]R<RXN#0H-"BTM(%1E<W0@.1E13+BXN#0IS96QE8W0@.5&5X=$-O
M;"!F<F]M($944U!U;F,-"B\J#0I497AT0V]L(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.( T*+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2 -"D9L870@.36]N:71O<G,@.)B!4;W-H:6)A(&UA9VEC
M#0I&;&%T($UO;FET;W)S('P@.5&]S:&EB82!M86=I8PT*1FQA="!-;VYI=&]R
M<R!A;F0@.5&]S:&EB82!M86=I8PT*1FQA="!-;VYI=&]R<R!O<B!4;W-H:6)A
M(&UA9VEC#0I&;&%T($UO;FET;W)S(",@.5&]S:&EB82!M86=I8PT*1FQA="!-
M;VYI=&]R<R E(%1O<VAI8F$@.;6%G:6,-"D9L870@.36]N:71O<G,@.*B!4;W-H
M:6)A(&UA9VEC#0I&;&%T($UO;FET;W)S('!L86-E:&]L9&5R(%1O<VAI8F$@.
M;6%G:6,-"F9L870@.;6]N:71O<G,-"G1H:7,@.:7,@.=&AE('1H:7)D(')O=PT*
M5&]S:&EB82!M86=I8PT*;6%G:6,-"E1O<VAI8F$-"@.T**#$S(')O=RAS*2!A
M9F9E8W1E9"D-"BHO#0H-"BTM(&9R965T97AT( T*4T5,14-4("H@.1E)/32!&
M5%-0=6YC('=H97)E($92145415A4*"HL)R)&;&%T($UO;FET;W)S( "8@.5&]S
M:&EB82!M86=I8R(G*2 M+2 Q,B!R;W=S+"!O<F=I86YL('%U97)Y#0H-"E-%
M3$5#5" J($923TT@.1E134'5N8R!W:&5R92!&4D5%5$585"@.J+"<B1FQA= "!-
M;VYI=&]R<R!\(%1O<VAI8F$@.;6%G:6,B)RD@.+2T@.(#$R(')O=W,L('=I= &@.@.
M*B H87-T<FEC:RD@.;W(@.?" H8F%R*0T*#0I314Q%0U0@.*B!&4D]-($944U!U
M;F,@.=VAE<F4@.1E)%151%6%0H*BPG(D9L870@.36]N:71O<G,@.;W(@.5&]S:&EB
M82!M86=I8R(G*2 M+2 Q,B!R;W=S+"!W:71H($]2#0H-"BTM(&-O;G1A:6YS
M#0I314Q%0U0@.*B!&4D]-($944U!U;F,@.=VAE<F4@.0T].5$%)3E,H*BPG(D9L
M870@.36]N:71O<G,@.)B!4;W-H:6)A(&UA9VEC(B<I("TM(#4@.<F]W<R!W:71H
M(&-O;G1A:6YS+"!P=6YC='5A=&EO;B!I<R!I9VYO<F5D#0H-"E-%3$5#5" J
M($923TT@.1E134'5N8R!W:&5R92!#3TY404E.4R@.J+"<B1FQA= "!-;VYI=&]R
M<R C(%1O<VAI8F$@.;6%G:6,B)RD@.+2T@.("!R;W=S('=I=&@.@.8V]N=&%I;G,@.
M86YD(&%N>2!P=6YC='5A=&EO;B!I<R!I9VYO<F5D#0H-"E-%3$5#5" J($92
M3TT@.1E134'5N8R!W:&5R92!#3TY404E.4R@.J+"<B1FQA="!-;VYI=&]R<R!O
M<B!4;W-H:6)A(&UA9VEC(B<I("TM(#,@.<F]W<R!W:71H(&-O;G1A:6YS+"!/
M4B!I<R!I9VYO<F5D+"!/4B!I;B!N;VES92YE;G4-"@.T*4T5,14-4("H@.1E)/
M32!&5%-0=6YC('=H97)E($-/3E1!24Y3*"HL)R)4;W-H:6)A("H@.;6%G:6,B
M)RD@.+2T@..2!R;W=S+"!W:71H(&%N9"!W:71H;W5T("(J(B!I; B!B971W965N
M('-E87)C:"!W;W)D<PT*#0H-"@.T*+2T@.4F5M;W9E($94($EN9&5X97,@.)B!#
M871A;&]G("8@.=&%B;&4N+@.T*97AE8R!S<%]F=6QL=&5X=%]T86)L92 G1E13
M4'5N8R<L)V1R;W G#0IE>&5C('-P7V9U;&QT97AT7V-A=&%L;V<@.)T944U!C
M0V%T86QO9R<L)V1R;W G#0IG;PT*9')O<"!T86)L92!&5%-0=6YC#0IG;PT*
`
end