asked on

How to get all hits with apostrophe and colon? Example: Andersons, Anderson's, Anderson:s, Andersons'

In Sweden we have no special genitive characters i.e. Anderson's cat is just written Andersons cat (or Andersons katt in Swedish). But some companies write their names with apostrophe ' and sometimes even colons i.e. Anderson's or Anderson:s (or Mc Donald's for example).

In our company name SQL query we use a combination of ordinary search and full text search with the "contains("formsof thesaurus")". When searching for andersons, no hits for anderson's or anderson:s are returned and this is our problem. Likewise when search for example anderson's, no hits for andersons (or anderson:s) are returned.

I have tried to set this in the thesaurus file but it seems to just consider whole words and it is not working for example to set s 's :s.

One way would be to check the incoming search word(s) programatically for apostrophes or colons and do a search for each. That is; if @searchWord= Anderson's then search for Anderson's then replace the apostrophe to a colon and search for Anderson:s and then remove the colon to search for Andersons but this doesn't seem to be a good solution performance wise...?

Another way would be to put Andersons Anderson's <subAnderson:s> in the full text search thesaurus file but this is not a realistic solution since there are so many variants; Svenssons, Peters, Harrys etc. etc. to be set in that file.

How do I solve this problem?

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ClaburP

ASKER

Thanks! That really works exactly as I want. But I also would like to incorporate this into a full text search.
The query now is like below:

set @tempVadvar='FORMSOF(THESAURUS, " ' + @searchWord + ' ")'
SELECT * FROM CompanyTable WHERE CONTAINS (CompanyName, @tempVadVar)

I have tried to apply your solution and still be able to use the full text search index, but quite not succeeded with that.

It would be much appreciated if you have any input on how to do that.

Thankyou

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ClaburP

ASKER

Ok, thanks again! Well there are some problems with that solution. Firstly, our query is within a stored procedure so I can't use a view. But of course I could create a table variable or temporary table instead. But then the new problem is that I can not use the full text search with FORMSOF(THESAURUS) on the @table variable since it is not full text indexed.

One solution would be to create a new column "StrippedCompanyName" in the static CompanyTable with the stripped company names in it, and then do the full text search on that column.
But I was hoping there is better and smoother solution than that... :-)

ClaburP

ASKER

Hello. This topic is not abandoned but I think I only have got a partial solution in the answers. That is why I have doubts in marking it accepted solution. Is there a button "Partially solved"...?

ClaburP

ASKER

Only partially solved. Not solved when using with Fulltext search.

Guy Hengel [angelIII / a3]

>Is there a button "Partially solved"...?
no.

from my experience, the only "solution" is to have 2 columns:
1 with the original value
1 with the value with the ":" and the "'" removed (possibly in a dedicated table...)

and using the fulltext on that second column (and not the original one)
it will use twice the space, but help to solve this and similar problems...