I'm looking to compare two varchars in SQL, one would be something like Cafe
and the other Café
is there a way in SQL that will allow the two values to be compared. For instance:
SELECT * FROM Venue WHERE Name Like '%cafe%'
So if there is a venue with the name Big Bobs Café Extraordinaire
it would be included in the result set?
Accent insensitive collation considers the accented and unaccented versions of letters to be identical for sorting purposes.
MSDN states COLLATE DATABASE_DEFAULT clause casts the collation of an expression, column definition, or database definition to inherit the collation of the "current database". To complement MSDN, the "current database" is the context of the database where the query is executed.
SQL Server installation configuration sets the default collation for the created instance (Latin1_General_CI_AI). New databases will be configured with the default collation of the SQL Server. Users can change the collation settings at the database level but not at the SQL Server level.
Coerce to an accent insensitive collation
You'll also need to ensure both side have the same collation to avoid errors or further coercions if you want to compare against a table variable or temp table varchar column
and because the constant value will have the collation of the database Update: only for local variables, not for constants nope, not even then
SELECT * FROM Venue WHERE Name COLLATE Latin1_general_CI_AI Like '%cafe%' COLLATE Latin1_general_CI_AI
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With