Searching foreign characters in MS SQL DB with ASP Classic

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!
Nico2011Asked:
Who is Participating?
 
Dale BurrellConnect With a Mentor DirectorCommented:
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.
0
 
Dale BurrellDirectorCommented:
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.
0
 
Nico2011Author 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?!
0
 
Nico2011Author Commented:
Dale - perfect - works a dream - thank you VERY MUCH!
0
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.

All Courses

From novice to tech pro — start learning today.