如何在 SQLServer 中执行重音不敏感的比较(e 与 & # 232; ，& # 233; ，& # 234; 和 & # 235;) ？

我希望比较 SQL 中的两个 varchars，一个类似于 Cafe，另一个类似于 Café，SQL 中有一种方法可以比较这两个值。例如:

SELECT *
FROM Venue
WHERE Name Like '%cafe%'

那么，如果有一个场地的名称是 Big Bobs Café Extraordinaire，它将被包括在结果集？

101045 次浏览
小开

By applying a specific accent insensitive collation to your select:

SELECT *
FROM Venue
WHERE Name COLLATE Latin1_General_CI_AI Like '%cafe%' COLLATE Latin1_General_CI_AI

The CI stands for "Case Insensitive" and AI for "Accent Insensitive".

小开
最佳答案

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
小开

Accent Sensitive and Accent Insensitive searching can be don by using Latin1_general_CI_AI

Here AI is for Accent Insensitive and AS for Accent Sensitive ie, Café and Cafe are the same if Accent Insensitive.

In the below query Latin1_general_CI_AI can be break down into the following parts.

  • latin1 makes the server treat strings using charset latin 1, basically ascii.

  • CI specifies case-insensitive, so "ABC" equals to "abc".

  • AI specifies accent-insensitive,so 'ü' equals to 'u'.

Your query should be as follows:

SELECT * FROM Venue WHERE Name COLLATE Latin1_general_CI_AI Like '%cafe%' COLLATE Latin1_general_CI_AI

Expected Result is as follows:

 Id  name
1  Café
2  Cafe

提交答案