We help IT Professionals succeed at work.

Searching foreign characters in MS SQL DB with ASP Classic

Nico2011
Nico2011 asked
on
Hello,

I a wondering if there is a way to search for foreign characters in a DB, so for example if someone is searching for Côte d'Azur, but a user with an English keyboard may type in Cote D'Azur, is there a simple way to query the DB by switching the foreign characters please?

Thanks!
Comment
Watch Question

Dale BurrellDirector

Commented:
I think by default that actually works. But assuming it doesn't you need to change the collation for the query.

Here are the possible collations: http://msdn.microsoft.com/en-us/library/ms144250.aspx

Here is how you change it for the query: http://msdn.microsoft.com/en-us/library/ms184391.aspx

You probably want Latin1_General_CI_AI which means uses the Latin1 character set CI = Case Insensitive and AI = Accent Insensitve.

Author

Commented:
Thanks Dale - I've read up and it sounds like that would be the solution, however when I try to update the DB, I get:

The database could not be exclusively locked to perform the operation.
ALTER DATABASE failed. The default collation of database 'nicoco_villas' cannot be set to Latin1_General_CI_AI

And I'm not sure how to lock the DB in order to perform this.. Also, am I doing the right thing?!
Director
Commented:
Sorry I wasn't clear about that, I'm not suggesting you change the collation in the database, I'm suggesting you change it in the query. Something like:

select *
from MyTable
where MyField collate Latin1_General_CI_AI like 'Cote D'Azur'

I'm not sure I have the syntax correct, but give google a shot if not, or ask again.

Author

Commented:
Dale - perfect - works a dream - thank you VERY MUCH!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.