如何在SQL Server中比较Unicode字符? [英] How to compare Unicode characters in SQL server?
问题描述
我正在尝试通过执行以下查询来查找数据库(SQL Server)中文本中具有字符é的所有行.
Hi I am trying to find all rows in my database (SQL Server) which have character é in their text by executing the following queries.
SELECT COUNT(*) FROM t_question WHERE patindex(N'%[\xE9]%',question) > 0;
SELECT COUNT(*) FROM t_question WHERE patindex(N'%[\u00E9]%',question) > 0;
但是我发现了两个问题:(a)他们两个都返回不同数量的行,并且(b)他们都返回没有指定字符的行.
But I found two problems: (a) Both of them are returning different number of rows and (b) They are returning rows which do not have the specified character.
构造正则表达式并比较Unicode正确吗?
Is the way I am constructing the regular expression and comparing the Unicode correct?
问题列使用数据类型nvarchar
存储.
但是,以下查询给出了正确的结果.
The question column is stored using datatype nvarchar
.
The following query gives the correct result though.
SELECT COUNT(*) FROM t_question WHERE question LIKE N'%é%';
推荐答案
为什么不使用SELECT COUNT(*) FROM t_question WHERE question LIKE N'%é%'
?
NB:Like
和patindex
不不接受正则表达式.
NB: Like
and patindex
do not accept regular expressions.
在SQL Server模式语法中,[\xE9]
表示匹配指定集合中的任何单个字符.即匹配\
,x
,E
或9
.因此,以下任何字符串都将匹配该模式.
In the SQL Server pattern syntax [\xE9]
means match any single character within the specified set. i.e. match \
, x
, E
or 9
. So any of the following strings would match that pattern.
- 大象"
- 轴"
- "99.9"
这篇关于如何在SQL Server中比较Unicode字符?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!