Searching by suffix, columns with dots and digits

Hello,

I have an ASP.NET application and SQL Server 2008 Web backend. I use
full-text catalog for searching and I'm looking for some advices.

The FT-indexed data table stores programs names and versions. It consists of
several columns: varchar(50) Name (like "OpenOffice") and varchar(50)
Version (like "2.0"). I would like to be able to search both by name and
name+version. For example, the search results should contain OpenOffice 2.0
after querying for "openoffice" or "openoffice 2.0". It should also search
by prefix (OpeOffice 2.0 should come up after searching for "open") and
suffix (searching for "office").

Now to partially accomplish that scenario I have another column called
FullTextName and it stores Name and Version values combined without the dots
(e.g. "OpenOffice 20"). When the user types "openoffice 2.0" in the search
box, I add the asterisks, divide it to '"openoffice *" AND "20 *"' and
search in FullTextName column using CONTAINSTABLE. It works, however I don't
know how to search by suffix - when the user types "office" he's unable to
find "OpenOffice". I've come up with the idea of storing another column with
reversed Name, but then the Version column is causing some further
problems...

Could anyone give me some hints about how to do it so that it will work
fine? I believe Full-Text is a powerfull tool and there must be some way
to do it right.

Thank you very much for any suggestions and comments.

Regards,
Wojciech

First off - what are you doing using OpenOffice;) This is an MS platform for
gosh sakes.

Secondly searching on Open* should return hits to OpenOffice.

You can't search on suffixes. This might ship in another version of the
product as it is a frequently demanded feature.

Most of the time when I have to search on suffixes I do the reverse trick
you mention.


"Wojciech Kowasz" wrote in message

> Hello,
>
> I have an ASP.NET application and SQL Server 2008 Web backend. I use
> full-text catalog for searching and I'm looking for some advices.
>
> The FT-indexed data table stores programs names and versions. It consists
> of several columns: varchar(50) Name (like "OpenOffice") and varchar(50)
> Version (like "2.0"). I would like to be able to search both by name and
> name+version. For example, the search results should contain OpenOffice
> 2.0 after querying for "openoffice" or "openoffice 2.0". It should also
> search by prefix (OpeOffice 2.0 should come up after searching for "open")
> and suffix (searching for "office").
>
> Now to partially accomplish that scenario I have another column called
> FullTextName and it stores Name and Version values combined without the
> dots (e.g. "OpenOffice 20"). When the user types "openoffice 2.0" in the
> search box, I add the asterisks, divide it to '"openoffice *" AND "20 *"'
> and search in FullTextName column using CONTAINSTABLE. It works, however I
> don't know how to search by suffix - when the user types "office" he's
> unable to find "OpenOffice". I've come up with the idea of storing another
> column with reversed Name, but then the Version column is causing some
> further problems...
>
> Could anyone give me some hints about how to do it so that it will work
> fine? I believe Full-Text is a powerfull tool and there must be some
> way to do it right.
>
> Thank you very much for any suggestions and comments.
>
> Regards,
> Wojciech