Solved

Searching foreign characters in MS SQL DB with ASP Classic

Posted on 2012-03-10
4
353 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 500 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Debugging Html 8 31
How to post data to an API using ASP Classic 3 27
sql query 5 43
StoredProcedure to JSON query faulty syntax 2 24
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

762 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