Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dd
Starting Member
5 Posts |
 Posted - 2009-02-04 : 09:39:26
|
| I want to write an accent insensitive (AI) query on a database whose collation could be anything.Take Turkish as an instance of "anything".The MSSQL server will have been installed on a Turkish PC and I assume that the server will adopt a Turkish collation and that databases will be created with the server <default collation> which will be Turkish.I need ordering/sorting to obey a Turkish collation so that they are correct according to the Turkish alphabet so all should be well. But I want to do an AI search using "LIKE".My first thought is to ask for the collation of the database or server ( I assume they will be the same ) and then use that collation with my "LIKE" having changed any AS to AI. i.e. my code doesn't care if its Turkish or French or German it will just use whatever it finds but make it AI.But am I guaranteed to get a collation name that contains AS or AI from MSSQL server 2000/2005/2008 ?Is there a general way to query AI whilst leaving other aspects of the collation as you find them ?Any thoughts much appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-04 : 09:42:23
|
you can always force an accent insensitive during search using COLLATE clause . something likeWHERE Column COLLATE <AI collation here> LIKE '%some pattern%' |
 |
|
|
dd
Starting Member
5 Posts |
Posted - 2009-02-04 : 09:49:56
|
| Thanks for the reply.I know about the COLLATE clause, as I say in my original post "then use that collation with my LIKE"...The problem is that I cant hard code a collation name because I don't know what it is until run time. I may be running on a Turkish database, I may be running on a French database.Thanks again |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-04 : 09:54:06
|
then use WHERE Column COLLATE database_default LIKE '%some pattern%' |
|
|
|
dd
Starting Member
5 Posts |
Posted - 2009-02-04 : 09:57:40
|
| but the database_default may not be AI ? |
|
|
|
dd
Starting Member
5 Posts |
Posted - 2009-02-04 : 10:01:50
|
| What I think I want is a use database_default_but_make_it_AI ! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-04 : 10:02:00
|
| what? then what determines what collation you need to use for that db? |
|
|
|
dd
Starting Member
5 Posts |
Posted - 2009-02-04 : 10:10:18
|
| If the database is Turkish_CI_AS I need to use Turkish_CI_AI.If the database is French_CI_AS I need to use French_CI_AI.ie leave all aspects of collation as they are but force AI.I had thought though maybe I could ask for the collation name and then use it with my "LIKE" having changed any AS to AI. |
|
|
|
|
|
|
Accent insensitive for any collation.