Still returns zero records. if i do select *from author WHERE CONTAINS(authorLastName, N'"LYNE"') i get 6 records, one of those has a forename of B
If i do select * from author WHERE CONTAINS(authorForeName, N'"B"') i get zero records If I do select * from author WHERE CONTAINS(authorForeName, N'"*B*"') I get 5 results but none of them are just forename of B wierd..... ___________________ Eric Jones aka jonese http://www.jonese.us http://twitter.com/jonese On Fri, Jan 23, 2009 at 4:05 PM, Teddy R. Payne <teddyrpa...@gmail.com>wrote: > Ok, try this then if you are using SQL Server: > > USE WhateverYourDatabaseIs; > GO > > DECLARE > @foreName nvarchar(100) > , @lastName nvarchar(100) > > SELECT > @foreName = N'B' > , @lastName = N'Lyne' > > SELECT > * > FROM > author > WHERE > CONTAINS(authorForeName, @foreName) AND CONTAINS(authorLastName, > @lastName) > > > > Teddy R. Payne, ACCFD > Google Talk - teddyrpa...@gmail.com > > > > On Fri, Jan 23, 2009 at 3:51 PM, jonese <gig...@gmail.com> wrote: > >> Msg 102, Level 15, State 1, Line 3 >> Incorrect syntax near 'N'B''. >> ___________________ >> Eric Jones aka jonese >> http://www.jonese.us >> http://twitter.com/jonese >> >> >> On Fri, Jan 23, 2009 at 3:48 PM, Teddy R. Payne >> <teddyrpa...@gmail.com>wrote: >> >>> SELECT * >>> FROM author >>> WHERE CONTAINS(authorForeName, "N'B'") AND CONTAINS(authorLastName, >>> "N'LYNE'") >>> >>> Did you try the above query? Which is a slight derivation on yours. >>> >>> Teddy R. Payne, ACCFD >>> Google Talk - teddyrpa...@gmail.com >>> >>> >>> >>> On Fri, Jan 23, 2009 at 3:42 PM, jonese <gig...@gmail.com> wrote: >>> >>>> Teddy, >>>> Nope the quotes are all ok. the value is in single quotes after the N >>>> (yes it's nVarChar) and the double quote are for phrases. In my example i >>>> could have removed the double quotes and gotten the same results etc. >>>> >>>> The syntax appears to be A-OK because i get no errors. I'm just not >>>> getting why i can't search on MULTIPLE columns with MULTIPLE values which >>>> are in a fulltext index.. >>>> ___________________ >>>> Eric Jones aka jonese >>>> http://www.jonese.us >>>> http://twitter.com/jonese >>>> >>>> >>>> On Fri, Jan 23, 2009 at 3:36 PM, Teddy R. Payne < >>>> teddyrpa...@gmail.com> wrote: >>>> >>>>> What is the data type of the columns you are referencing? >>>>> >>>>> (authorForeName, N'"B"') The N there I believe is a reference for >>>>> nVarchar if you are allowing i10n compatibility. >>>>> >>>>> The syntax looks wonky as I thought it was only a single quote like >>>>> N'B' >>>>> >>>>> Then you can wrap it up in double quotes like "N'B'" >>>>> >>>>> So I suspect you quotes are out of order. Double quote then the N then >>>>> the single quote then the literal string then single quote then double >>>>> quote. Savvy? >>>>> >>>>> Teddy R. Payne, ACCFD >>>>> Google Talk - teddyrpa...@gmail.com >>>>> >>>>> >>>>> >>>>> On Fri, Jan 23, 2009 at 3:04 PM, jonese <gig...@gmail.com> wrote: >>>>> >>>>>> I have two columns authorForeNAme and authorLastName and i'm trying to >>>>>> search against a Full Text index to find a specific author. >>>>>> i thought i could do this: >>>>>> >>>>>> SELECT * >>>>>> FROM author >>>>>> WHERE CONTAINS(authorForeName, N'"B"') AND CONTAINS(authorLastName, >>>>>> N'"LYNE"') >>>>>> >>>>>> But it returns zero records. however if i do >>>>>> >>>>>> select * >>>>>> from author >>>>>> WHERE authorForeName = 'B'' AND CONTAINS(authorLastName, N'"LYNE"') >>>>>> >>>>>> I get my desired results. >>>>>> >>>>>> Why doesn't the first query work as expected? >>>>>> ___________________ >>>>>> Eric Jones aka jonese >>>>>> http://www.jonese.us >>>>>> http://twitter.com/jonese >>>>>> >>>>> >>>>> >>>> >>> >> >