What's the difference between SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP1_CI_AI
AI stands for accent insensitive (i.e. determines if cafe = café).
You can use the collate keyword to convert one (or both) of the values' collations.
See link for more info: http://msdn.microsoft.com/en-us/library/aa258237(v=sql.80).aspx
Example: DBFiddle
--setup a couple of tables, populate them with the same words, only vary whether to accents are included
create table SomeWords (Word nvarchar(32) not null)
create table OtherWords (Word nvarchar(32) not null)
insert SomeWords (Word) values ('café'), ('store'), ('fiancé'), ('ampère'), ('cafétería'), ('fête'), ('jalapeño'), ('über'), ('zloty'), ('Zürich')
insert OtherWords (Word) values ('cafe'), ('store'), ('fiance'), ('ampere'), ('cafétería'), ('fete'), ('jalapeno'), ('uber'), ('zloty'), ('Zurich')
--now run a join between the two tables, showing what comes back when we use AS vs AI.
--NB: Since this could be run on a database of any collation I've used COLLATE on both sides of the equality operator
select sw.Word MainWord
, ow1.Word MatchAS
, ow2.Word MatchAI
from SomeWords sw
left outer join OtherWords ow1 on ow1.Word collate SQL_Latin1_General_CP1_CI_AS = sw.Word collate SQL_Latin1_General_CP1_CI_AS
left outer join OtherWords ow2 on ow2.Word collate SQL_Latin1_General_CP1_CI_AI = sw.Word collate SQL_Latin1_General_CP1_CI_AI
Example's Output:
MainWord
MatchAS
MatchAI
café
cafe
store
store
store
fiancé
fiance
ampère
ampere
cafétería
cafétería
cafétería
fête
fete
jalapeño
jalapeno
über
uber
zloty
zloty
zloty
Zürich
Zurich
Memor-X
"There's no such thing as Gods, just people with stupidly large amounts of power" - RATHROR Avatar - OC Commission by PraVDa-pxiv Indie game developer/author, currently working on a game and book. https://pronoun.is/she Huge Otaku for Games, Anime, Manga, etc. Collects figures/merchandise with a paranoia that compels me to buy 2 of any collectable i can't duplicate easily. Enjoys most genres however has a bias towards Yuri (百合, "lily") and will favour it over others. Currently Watching She-ra, Naruto, Rick and Morty Currently Reading Breath of Flowers Currently Playing [PS4] - Tales of zestiria [PS5] - Returnal [3DS] - Etrian Odyssey 4 [PC] - The Legend of Heroes: Trails in the Sky The Third, Final Fantasy XIV Current Projects Nexis Core [Game] Avaria Core [Book] Longplays The Legend of Heroes: Trails in the Sky Ys I & II Ys: Origin Shin Megami Tensei - Digital Devil Saga Shin Megami Tensei - Digital Devil Saga II Final Fantasy IX
Updated on July 17, 2022Comments
-
Memor-X almost 2 years
i get this error when i run an update query in Microsoft SQL Server
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation.
the query uses only 2 tables, the table it's updating and a temp table which it does an inner join into, neither table have i specified the collation of and they are both on the same database which means they should have the same collation since's it should be the database default one right
looking at the collations, the only difference is the last character, all i understand of the last part is that CI stands for Case Insensitive. if i was to take a stab in the dark i would think AI stands for Auto Increment but i have no idea what AS stands for