T-SQL匹配特殊字符。 Pre应匹配Pré

时间:2022-01-10 10:18:17

I have a list of company names and the user has to enter his company name to get into the system. Let's say we have the company "Pré ABC", now I want the user to be able to type "Pre" or "Pré".

我有一个公司名称列表,用户必须输入他的公司名称才能进入系统。假设我们有公司“PréABC”,现在我希望用户能够键入“Pre”或“Pré”。

First I thought this was build-in functionality of the LIKE statement, but unfortunately it isn't. Any thoughts?

首先我认为这是LIKE语句的内置功能,但遗憾的是它不是。有什么想法吗?

2 个解决方案

#1


This has to do with collation. Each database has its own collation (and any column can override that collation, too). In your case, you're looking for a collation that's not accent-sensitive, and not case-sensitive. Try configuring the database to "SQL_Latin1_General_CP1_CI_AI". That decodes as "code page 1, case-insensitive, accent-insensitive", which should make your queries work as desired.

这与整理有关。每个数据库都有自己的排序规则(任何列都可以覆盖该排序规则)。在您的情况下,您正在寻找一种非重音敏感的排序规则,而不是区分大小写。尝试将数据库配置为“SQL_Latin1_General_CP1_CI_AI”。解码为“代码页1,不区分大小写,不区分重音”,这应该使您的查询按预期工作。

#2


SELECT  1
WHERE   N'Pré ABC' COLLATE LATIN1_GENERAL_CI_AI LIKE N'%Pre%' 

#1


This has to do with collation. Each database has its own collation (and any column can override that collation, too). In your case, you're looking for a collation that's not accent-sensitive, and not case-sensitive. Try configuring the database to "SQL_Latin1_General_CP1_CI_AI". That decodes as "code page 1, case-insensitive, accent-insensitive", which should make your queries work as desired.

这与整理有关。每个数据库都有自己的排序规则(任何列都可以覆盖该排序规则)。在您的情况下,您正在寻找一种非重音敏感的排序规则,而不是区分大小写。尝试将数据库配置为“SQL_Latin1_General_CP1_CI_AI”。解码为“代码页1,不区分大小写,不区分重音”,这应该使您的查询按预期工作。

#2


SELECT  1
WHERE   N'Pré ABC' COLLATE LATIN1_GENERAL_CI_AI LIKE N'%Pre%'