具有字母范围的PATINDEX排除变音符号（带重音符号） [英] PATINDEX with letter range exclude diacritics (accented characters)

问题描述

我正在尝试找出如何使用patindex查找一系列字母字符，但不包括带重音符号的字符。如果我直接搜索，则使用默认排序规则（不敏感）即可。但是，当我搜索一系列字母时，它将与重音字符匹配

I am trying to figure out how to use a patindex to find a range of letter characters, but exclude accented characters. If I do a straight search, using the default collate (insensitive) works just fine. However, when I search a range of letters, it will match on the accented character

SELECT
    IIF('Ú' = 'U' COLLATE Latin1_General_CI_AI, 'Match', 'No') AS MatchInsensitive,
    IIF('Ú' = 'U' COLLATE Latin1_General_CI_AS, 'Match', 'No') AS MatchSensitive,
    PATINDEX('%[A-Z]%', 'Ú' COLLATE Latin1_General_CI_AI)      AS PIInsensitive,
    PATINDEX('%[A-Z]%', 'Ú' COLLATE Latin1_General_CI_AS)      AS PISensitive

将给出以下结果：

MatchInsensitive MatchSensitive PIInsensitive PISensitive
---------------- -------------- ------------- -----------
Match            No             1             1

什么我真的想做的是确定字符串中带重音符号的字符位置，所以我真的在搜索 PATINDEX（'％[^ A-Z0-9]％'）。

What I am really trying to do is to identify the character position of accented characters in a string, so I was really searching for PATINDEX('%[^A-Z0-9 ]%').

如果我有以下查询，我希望2 SELECT PATINDEX（'％[^ A-Z0-9]％'，'médico'）的结果，但我得到0。

If I have the following query, I would expect a result of 2 SELECT PATINDEX('%[^A-Z0-9 ]%', 'médico'), but I get 0.

推荐答案

您可以使用二进制排序规则，例如 Latin1_General_100_BIN2 。

You could use a binary collation, e.g. Latin1_General_100_BIN2.

select patindex('%[^a-zA-Z0-9 ]%', 'médico' collate Latin1_General_100_BIN2)

上一个月： http://rextester.com/ZICLN98474

返回 2

这篇关于具有字母范围的PATINDEX排除变音符号（带重音符号）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！