ignore special characters like ë,é,è,ä,á,à

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ignore special characters like ë,é,è,ä,á,à

Author

Topic

meanrat
Starting Member

3 Posts

Posted - 2007-02-09 : 03:48:09

Dear people

I was just asked to adjust a search engine I built in ASP. At this moment when you enter the word "financiele" it doesn't find the entries with the words "financiële". You have to spell words with umlauts and accent graves correctly with the special characters in order to get a result. And you know users...they do not always want to focus on spelling.

So I think it's indeed a good idea to give users the possibility to enter words like "financiele" and still find the entries with "financiële". Just like Google does! But how on earth is this possible?? I heard from someone it's possible to disable a function on the SQL server. But that function is only to be unchecked when you begin installing the software. And a complete reïnstall is definately not an option.

So I was hoping someone could give me a hint as to work-around this problem. Or maybe there IS a 100% solid solution to this? I would be happy to get acquainted with it.

Thanks for your help!

Regards,
Maarten (Holland)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-09 : 03:54:18

You can make use of DIFFERENCE function, I guess.

SELECT DIFFERENCE('financiele', 'financiële')

----
4

The return value 4 indicates both words have similar SOUNDEX values.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

meanrat
Starting Member

3 Posts

Posted - 2007-02-09 : 04:02:48

Thanks for a quick reply.

Fortunately this search engine I built should contain a small amount of words with such characters. It crossed my mind using the solution you gave me just now. Probably I would, if there won't be any better ony coming my way, but I think we all agree, it's not a pretty one. Maybe in the future there will be added a new entry to the database with another word containing such a special character... Using this solution we have to keep track of all different words containing umlauts and accent graves.

Another solution I just thought of was this.
This search engine I'm talking about is about downloadable files. Administrators can upload certain files and give a description with it. If I would add another Field to the download-table on the SQL server, containing a copy of the description field, but then all special characters are converted to a 'normal' letter.

So, if 'description-A' would be "financiële diensten", the field 'description-B' will be "financiele diensten". The search from a user is done on both fields, but the result given will be from the field 'description-A'.

Still I hope someone knows a better solution.

Regards,
Maarten

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 04:45:47

Or try the COLLATE keyword?

declare @a table (i varchar(20), j varchar(20))
insert @a values('financiele', 'financiële')

select * from @a

select * from @a
where i = j

select * from @a
where i <> j

select * from @a
where i collate SQL_Latin1_General_Cp1_CI_AI = j collate SQL_Latin1_General_Cp1_CI_AI

select * from @a
where i collate SQL_Latin1_General_Cp1_CI_AI <> j collate SQL_Latin1_General_Cp1_CI_AI

Peter Larsson
Helsingborg, Sweden

meanrat
Starting Member

3 Posts

Posted - 2007-02-09 : 05:04:53

[code]
SELECT *
FROM tpuser.downloads
WHERE (descr LIKE '%financiele%'
COLLATE SQL_Latin1_General_Cp1_CI_AI)
[/code]

Thanx Peter Larsson!
The above query with your tip on the COLLATE function worked very well. A colleague of mine came upon this solution as well, by the way, but other people said this would not work because...we had to install the whole SQL server again. So yes, the solution I was talking about earlier (where we thought to have to re-install the whole SQL server) turned out to be the right one after all.

Topic closed!
Thanx again

Regards,
Maarten

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources