Returning word forms?

Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I have a list of movie titles. If I do a search on "drive" I get all the
titles that have that word (i.e. "Drive My Car") in them, but I miss titles
like "She Drives Me Crazy"
Is there some switch I'm missing somewhere, or does the MS Fulltxt engine
not do stemming? (The T-SQL help file mentions it on one page, but doesn't
offer much detail...)
I'm using the CONTAINS keyword to do the search. Should I be using
something else?
declare @.s varchar(8000)
set @.s = 'SELECT dbo.main.FILM, dbo.main.FILMYEAR, dbo.aka_film.TITLE2 as
akatitle FROM dbo.main LEFT OUTER JOIN dbo.aka_film ON dbo.main.FILMID =
dbo.aka_film.FILMID
WHERE contains(main.film, ''' + @.film + ''')
GROUP BY dbo.main.FILM, dbo.main.FILMYEAR, dbo.aka_film.TITLE2 ORDER BY
dbo.main.FILM'
exec (@.s)
[Above SQL statement broken into lines for easier reading]
Also, has anyone (third-party?) implemented a "Did you mean..." type of
thing like Google has where it suggests potential mispellings or related
searches?
Many thanks,
Jeff
"J. Knapp" <jknapp@.nospam.nospam> wrote in
news:Xns95F66B22E1C1Fjknappnospamnospam@.207.46.248 .16:

> I'm using the CONTAINS keyword to do the search. Should I be using
> something else?
Can I answer my own question and say "freetext" seems to do the trick on
this one...
I'm still curious about a "Did you mean..." engine...
|||You need to either use best bests - see Sharepoint's implementation of this
and roll your own, or do a spell check - like
http://www.spellcheck.net/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"J. Knapp" <jknapp@.nospam.nospam> wrote in message
news:Xns95F66C884D76Ejknappnospamnospam@.207.46.248 .16...
> "J. Knapp" <jknapp@.nospam.nospam> wrote in
> news:Xns95F66B22E1C1Fjknappnospamnospam@.207.46.248 .16:
>
> Can I answer my own question and say "freetext" seems to do the trick on
> this one...
> I'm still curious about a "Did you mean..." engine...

Returning long string in varchar using Coalesce()

Hi,
I want to return a string of words separated by comma using coalesce() so
that the client application can look for a specific word in this long
string.Following is the script:-
CREATE TABLE WordList
(
WordId int IDENTITY,
WordDesc varchar(100)
)
INSERT INTO WordList VALUES ('Lion')
INSERT INTO WordList VALUES ('Goat')
INSERT INTO WordList VALUES ('Snake')
DECLARE @.WordList varchar(8000)
SELECT @.WordList = COALESCE(@.WordList + ', ', '') + WordDesc
FROM WordList
SELECT @.WordList AS WordList
Above approach works fine.However,as the maximum size for varchar is
8000,any longer string will be truncated.I can not use "text" datatype for
this as it is not allowed.
Any workaround?Any pointers?you cannot declare a variable of text datatype. Of course in 2005 I think yo
u
can do that because its been replaced as varchar(max). Hope this helps.
"Ashish" wrote:

> Hi,
> I want to return a string of words separated by comma using coalesce() so
> that the client application can look for a specific word in this long
> string.Following is the script:-
>
> CREATE TABLE WordList
> (
> WordId int IDENTITY,
> WordDesc varchar(100)
> )
> INSERT INTO WordList VALUES ('Lion')
> INSERT INTO WordList VALUES ('Goat')
> INSERT INTO WordList VALUES ('Snake')
>
> DECLARE @.WordList varchar(8000)
> SELECT @.WordList = COALESCE(@.WordList + ', ', '') + WordDesc
> FROM WordList
> SELECT @.WordList AS WordList
>
> Above approach works fine.However,as the maximum size for varchar is
> 8000,any longer string will be truncated.I can not use "text" datatype for
> this as it is not allowed.
> Any workaround?Any pointers?
>|||Correct.Thank you.I am aware of the same.But i am using Sql 2000 and looking
for a workaround for this.
"Omnibuzz" wrote:
> you cannot declare a variable of text datatype. Of course in 2005 I think
you
> can do that because its been replaced as varchar(max). Hope this helps.
> --
>
>
> "Ashish" wrote:
>|||Perform concatenation at the client / presentation layer?

> Above approach works fine.However,as the maximum size for varchar is
> 8000,any longer string will be truncated.I can not use "text" datatype for
> this as it is not allowed.
> Any workaround?Any pointers?|||I want to avoid returning resultset to the presentatio layer.If i would want
i would make use of the resultset only.No concatenation would be required.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Perform concatenation at the client / presentation layer?
>
>
>
>|||SQL Server is not a text/image editor. If your content can easily be
concatenated, why is it not stored in one piece?
You could create a temporary table, and then use READTEXT/UPDATETEXT to
concatenate the values in SQL. This can hardly perform as well as a client
application.
ML
http://milambda.blogspot.com/