473,902 Members | 3,721 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to make full-text search accent-insensitive?

My SQL Server 2000 does not use the accent insensitive collation setting
(collation containing _AI) in full-text serches:
While SELECT * FROM <table> WHERE <column> LIKE '%a%' returns 'Mäuse',
SELECT * FROM <table> WHERE CONTAINS(*, 'a') does not.

Setting 'default full-text language' to neutral (0) does not help.

How can I make full-text searches accent insensitive?

Thanks for any ideas!
Matthias
Jul 20 '05 #1
3 18218

"Matthias HALDIMANN" <ma************ ****@epfl.ch> wrote in message
news:40******** @epflnews.epfl. ch...
My SQL Server 2000 does not use the accent insensitive collation setting
(collation containing _AI) in full-text serches:
While SELECT * FROM <table> WHERE <column> LIKE '%a%' returns 'Mäuse',
SELECT * FROM <table> WHERE CONTAINS(*, 'a') does not.

Setting 'default full-text language' to neutral (0) does not help.

How can I make full-text searches accent insensitive?

Thanks for any ideas!
Matthias


You'll probably get a better reply if you post this in
microsoft.publi c.sqlserver.ful ltext, since it's a relatively specialized
area.

Simon
Jul 20 '05 #2
The "solution" for those who are interested:

There is NO solution! This is a known bug, full-text search is ALWAYS
accent-sensitive. All you can do is wait for a future update that may
correct this.

Matthias

"Matthias HALDIMANN" <ma************ ****@epfl.ch> wrote in message
news:40******** @epflnews.epfl. ch...
My SQL Server 2000 does not use the accent insensitive collation setting
(collation containing _AI) in full-text serches:
While SELECT * FROM <table> WHERE <column> LIKE '%a%' returns 'Mäuse',
SELECT * FROM <table> WHERE CONTAINS(*, 'a') does not.

Setting 'default full-text language' to neutral (0) does not help.

How can I make full-text searches accent insensitive?

Thanks for any ideas!
Matthias

Jul 20 '05 #3
velum
1 New Member
Making accent insensitive searches with Full Text Search
Installing an accent insensitive version of Microsoft Search
Service

It is really a shame that Microsoft did not provide a solution for doing case
insensitive Full Text Search (FTS) before 2005. It is reaslly a lack of
consideration for all of their customers speaking or using language
that has accents. This is why I decided to post these instructions,
because there is a way around it. It is a post by Alex Hubner that
pointed me in the right direction. His solution works, I tried it.
He did not give the details on how to do it, so I decided I would:

* Get SharePoint Portal Server 2001 Service Pack 3 (SP3): KB837017 from
http://www.microsoft.com/downloads/d...15677a92-3470-
465f-9f63-e621094103e0&Di splayLang=en. There are five files to download:

o File Name: SPSFull1.exe
File Size: 27937 KB
o File Name: SPSFull2.exe
File Size: 25464 KB
o File Name: SPSFull3.exe
File Size: 25975 KB
o File Name: SPSFull4.exe
File Size: 27206 KB
o File Name: SPSFull5.exe
File Size: 25198 KB

You can download and unpack the five downloaded files, but in fact, you only
need the content of SPSFull3.exe. A directory called SharePointPorta lInstall will
be created.

Note: Do not use SharePoint Portal Server 2003, since its directory structure is
different, and the Microsoft Search Services don't seem to be a separate and
independent module in this version.

* Only the MS-Search part of the package is needed. You can find it in
SharePointPorta lInstall\Server \Search. The installation program is called
SearchStp.exe. This is the installation program we will use to re-install Microsoft
Search Service and make searches accent insensitive. This will allow, for
example, the get the same search results not matter if the user enters a keyword
with or without accents (Eg.: 'Montreal' or 'Montréal');
* You can use the documentation found on
http://support.microsoft.com/?kbid=827449 to re-install the Microsoft Search
Services. However, it is not necessary to go through all that trouble. I did so the
first time. However, some registry keys used by SQL Server and MS Search
Services were missing after. Gladfully, I had made a backup of all the registry
keys that we are asked to delete in this documentation. Also, the MS Search
Services have been installed into a different directory, so I had to do a search
through the registry to replace the old path by the new one where it had not been
updated by the installation program. In fact, you can skip this paragraph. I'm just
adding this information in case you would need it;
* Here are the registry keys I backup prior to this installation, just in case:

.HKEY_LOCAL_MAC HINE\Software\M icrosoft\Search
.HKEY_LOCAL_MAC HINE\System\Cur rentControlSet\ Services\MSSCNT RS
.HKEY_LOCAL_MAC HINE\System\Cur rentControlSet\ Services\MSSEAR CH
.HKEY_LOCAL_MAC HINE\System\Cur rentControlSet\ Services\MSSGAT HERER
.HKEY_LOCAL_MAC HINE\System\Cur rentControlSet\ Services\MSSGTH RSVC
.HKEY_LOCAL_MAC HINE\System\Cur rentControlSet\ Services\MSSIND EX

* The second time I installed the Accent Insensitive MS Search Services, I only
used section 2 of the documentation found on
http://support.microsoft.com/?kbid=827449 called Install the Microsoft Search
Service. However, I used the SearchStp.exe file downloaded earlier instead of
using the one they specify, and I stopped the Microsoft Search Service before
doing the installation even if it is not specified in the documentation. As specified
in this document, "To view the original domain name and user account, you can
use the most recent SQL Server setup log file (SqlstpN.log). In the SqlstpN.log
file, locate the line where the SQL Server Setup program ran the Ftsetup.exe
program. Additionally, make sure that the information is the same as the
information that is included in the command." On my computer, this file was
called sqlstp.log and it was located in the Windows directory.
* You can probably also follow the steps in section 3, but I haven't done so.

Once this is all done, you can use Enterprise Manager to create indexes on your database.
There is one thing though to know. When using Enterprise Manager to manage a remote
server, Tools->Full Text Indexing remains greyed out. I had to start Enterprise Manager
locally on the server in order to gain access to the Full Text Indexing tool.

Creating indexes
In order to create Full Text Search indexes on a table, the table needs to have a primary
key. So make sure all the tables you want to index have primary keys.

Here are the steps to create indexes. The first time you create an index, you will need to
create a catalog:

* Start Enterprise Manager and open the your database Table View;
* Select the table you want to index using FTS (Full Text Search) and right-click on
it.
* In the menu that just opened with the right-click, select Full-Text Index Table,
and then in the sub-menu select Define Full-Text Indexing on a Table. This will
start the Welcome to the SQL Server Full-Text Indexing Wizard.

* Click on Next;
* On the Next Screen, select the key to index, and click on Net;
* Select the field(s) to index and choose the proper language settings in the second
column. Click on Next;
* You will then have to create a Catalog (An FTS Catalog). Give it a name and
enter a location where it should be stored. You can use the default path. Click on
Next;
* You will then have to create a schedule for the indexation process. Click on New
Catalog Schedule, and define a Schedule. Let's make it a Full Population. Maybe
an Incremental Population would work, but I am not sure in that case what
happens if information is removed from the database being indexed. Once the
schedule is defined, click on Ok and then on Next;
* Click on Finish, and the catalogue will be created:

We are now ready to create an index on another table. This time, it is not necessary to
create a new catalogue. Simply reuse to one we previously created. Same thing for the
schedule; the one already created can be reused.

We now have a catalogue, but the indexes are still empty, since they are scheduled to be
generated in the future (based on the schedule you created). We should now do a full
population of the indexes. In SQL Server Enterprise Manager, right click on each table
you wish to index, select Full-Text Index Table, and then select Start Full Population.

You can now use a query similar to the one bellow in SQL Query Analyzer to test
whether the case insensitive indexing is working properly:

SELECT *
FROM table_name
WHERE CONTAINS(field_ name, 'raphaël')

o table_name should be replaced by the name of the table you want to search;
o field_name should be replaced by the name of the field you want to search;

Use this query with the same word with and without accents, and you should get the same
result both times.

Enjoy!

Jean-François Beauchamp
IT Consultant
jackojf-fts at yahoo.com




The "solution" for those who are interested:

There is NO solution! This is a known bug, full-text search is ALWAYS
accent-sensitive. All you can do is wait for a future update that may
correct this.

Matthias

"Matthias HALDIMANN" <matthias.haldi mann@epfl.ch> wrote in message
news:4039d459$1 @epflnews.epfl. ch...[color=blue]
> My SQL Server 2000 does not use the accent insensitive collation setting
> (collation containing _AI) in full-text serches:
> While SELECT * FROM <table> WHERE <column> LIKE '%a%' returns 'Mäuse',
> SELECT * FROM <table> WHERE CONTAINS(*, 'a') does not.
>
> Setting 'default full-text language' to neutral (0) does not help.
>
> How can I make full-text searches accent insensitive?
>
> Thanks for any ideas!
> Matthias
>
>[/color]
Jun 23 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

28
2847
by: AK | last post by:
Hi, I recently read an advice here that one should try to use make and version control system even if you're the only one working on the program. Is that a good advice? How many of you do that? Thanks, -AK
7
2339
by: Lowell Kirsh | last post by:
I have a script which I use to find all duplicates of files within a given directory and all its subdirectories. It seems like it's longer than it needs to be but I can't figure out how to shorten it. Perhaps there are some python features or libraries I'm not taking advantage of. The way it works is that it puts references to all the files in a dictionary with file size being the key. The dictionary can hold multiple values per key....
1
2136
by: Steve Juranich | last post by:
I'm in the process of writing a few extension types, and there's one that I'd sort of like to have getitem, setitem, getslice, setslice functionality for. I've been looking through the docs and how things are done in Objects/listobject.c, and it appears tha there are a couple of ways to do it and I'd like to get some opinions on what's the best way. As I see it, here are my options (please correct me if I'm wrong):
5
8446
by: Igor Solodovnikov | last post by:
Hi. I am trying to automatically backup transaction log when error 9002 happened. So i have created appropriate job and alert to catch this error. I have two instances of sql server under Windows 2000. One of them is full SQL Server, another is msde. When transaction log is full in full SQL Server error 9002 severity 17 state 2 is logged in sql server log and in Windows Application log. My alert firing my job. All is fine. But when...
1
1468
by: Tony Vasquez | last post by:
First off, I am coding soley for IE 5.5+ right now. I am going to try to later add code for other support of other browsers, but I doubt it will ever support more than a few. The site is up at http://www.classiccarsintl.com/_notes/ It will help explian what I am trying to do. In the photograph gallery, under "latest", is where I am testing out what I need. Once you see the page, simply click on the small image of a magnifying glass,...
6
7584
by: Nicky | last post by:
hi,all We are going to develop a program and when it is running, we need it full screen and also, user can not switch to other place before exit our program. I am thinking, we can make a window full screen, but how to avoid user switching to other program or windows? And also, there are many hot keys, how to capture or disable these things? Thanks
0
1194
by: Nicky | last post by:
hi,all We are going to develop a program and when it is running, we need it full screen and also, user can not switch to other place before exit our program. I am thinking, we can make a window full screen, but how to avoid user switching to other program or windows? And also, there are many hot keys, how to capture or disable these things? Thanks
3
1457
by: Alex Nitulescu | last post by:
_______________________________________________________________________________________ System.IO.FileNotFoundException: File or assembly name biypfduw.dll, or one of its dependencies, was not found. File name: "biypfduw.dll" at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Boolean isStringized, Evidence assemblySecurity, Boolean throwOnFileNotFound, Assembly locationHint, StackCrawlMark& stackMark) at...
19
2292
by: Swaregirl | last post by:
Hello, I would like to build a website using ASP.NET. I would like website visitors to be able to download code that I would like to make available to them and that would be residing on my personal server. Are there any code samples or books that someone can recommend so that I can implement this. I would prefer VB.NET code, but I am willing to convert from C# if necessary.
4
2029
by: robinsand | last post by:
Header File: car.h #if !defined CAR_H #define CAR_H enum TCarType { ctEconomy = 1, ctCompact, ctStandard, ctFullSize, ctMiniVan, ctSUV }; class Car { public: Car();
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10870
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10981
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7205
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5893
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6085
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4725
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4306
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3323
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.