?
Solved

Searching foreign characters in MS SQL DB with ASP Classic

Posted on 2012-03-10
4
Medium Priority
?
359 Views
Last Modified: 2012-03-13
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!
0
Comment
Question by:Nico2011
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 37704781
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
 

Author Comment

by:Nico2011
ID: 37704857
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
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 2000 total points
ID: 37705675
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
 

Author Closing Comment

by:Nico2011
ID: 37715225
Dale - perfect - works a dream - thank you VERY MUCH!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question