Link to home
Start Free TrialLog in
Avatar of basefilm
basefilm

asked on

Problem using COLLATE in queries. Other options?

I have a query that extracts the names of a number of users in our system.  Some of the users have non-U.S. characters in their names, like ë, é,  or ø.

I need to replace this with its closest equivalent U.S. character.  From my searches the best way to do this is using the COLLATE function, discussed in numerous threads on this site, and detailed on the MS site here:  http://msdn.microsoft.com/en-us/library/ms143726.aspx

My problem is that when I try to use COLLATE at all, I just get an error.  Now, I don't have direct access to the database.  I have access using a web-based tool in which I can write end edit SQL, but when I try to do even a simple query using COLLATE, I just get an error.

Is it possible the server I am using doesn't even support the COLLATE capability?

If it doesn't, what is another way I can use in a SELECT query to convert characters like æøåáälcçcédnoöruýtžš to aoaaalcccednooruytzs?

I mean, I guess I could, for each field, look at each character and if it is one of æøåáälcçcédnoöruýtžš replace it with its corresponding aoaaalcccednooruytzs.  That is problematic in that it seems dumb to have to do that, and inevitably a new international character will appear and break the export at some point.

Any ideas?

References:
http://blog.sqlpositive.com/2010/03/using-convert-with-collate-to-strip-accents-from-unicode-strings/
http://msdn.microsoft.com/en-us/library/ms143726.aspx
ASKER CERTIFIED SOLUTION
Avatar of basefilm
basefilm

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of basefilm
basefilm

ASKER

Answered own question.  Didn't mean to.  Don't claim to be an expert in SQL.