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 <sub>s</sub>      <sub>'s</sub>       <sub>:s</sub>.

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 <sub>Andersons</sub>        <sub>Anderson's</sub>         <subAnderson:s></sub>    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?
ClaburPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Something like this may work.  You can tweak to your needs.  The point is you can do replace on both down to Andersons without any puntuation and then compare.

If the replacements are more complex, you can create a User Defined Function that does the replacement and returns clean string that you can use in the comparisons.

Good luck.
SELECT *
FROM TableName
WHERE REPLACE(REPLACE(CompanyName, ':', ''), '''', '') LIKE '%' + REPLACE(REPLACE(@SearchWord, ':', ''), '''', '') + '%'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ClaburPAuthor Commented:
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

0
Kevin CrossChief Technology OfficerCommented:
I think the problem is that the REPLACE method is comparing the data after the replace has happened for both pieces of data whereas in the CONTAINS I think it will ultimately look at the original data which may be the one that has ' or : so REPLACE on the @searchWord isn't as effective and CompanyName in the CONTAINS statement must be a column name and not a formula.

{I don't recall ever trying this on a view, so forgive me if doesn't work as I don't have SQL to test right now.}

+Create a VIEW of CompanyTable which shows all the same data as CompanyTable but with CompanyName cleaned.  (e.g. SELECT *, REPLACE(REPLACE(CompanyName, ':', ''), '''', '') AS CompanyNameClean FROM CompanyTable)
+Alter your query to first clean the @searchWord before it is used and also use the VIEW for full text search:

SET @searchWord = REPLACE(REPLACE(@searchWord, ':', ''), '''', '')
SET @tempVadvar = 'FORMSOF(THESAURUS, " ' + @searchWord + ' ")'
SELECT * FROM CompanyView WHERE CONTAINS (CompanyNameClean, @tempVadVar)

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ClaburPAuthor Commented:
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... :-)
0
ClaburPAuthor Commented:
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"...?
0
ClaburPAuthor Commented:
Only partially solved. Not solved when using with Fulltext search.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.