在T-SQL中替换Unicode字符 [英] Replace Unicode characters in T-SQL

问题描述

如何仅替换字符串的最后一个字符：

How do I replace only the last character of the string:

select REPLACE('this is the news with a þ', 'þ', '__')

我得到的结果是：

__是______ code>

__is is __e news wi__ a __

EDIT
服务器和数据库的排序规则为 Latin1_General_CI_AS

我正在运行的实际查询是 REPLACE（note，'þ'，''）其中 note 是 ntext 列。关键是要删除刺字符，因为该字符将在以后的过程中用作列定界符。 （请不要建议更改定界符，考虑到它的使用范围，就不会发生这种情况！）

The actual query I'm running is REPLACE(note, 'þ', '') where note is an ntext column. The point is to strip out the thorn characters because that character gets used later in the process as a column delimiter. (Please don't suggest changing the delimiter, that's just not going to happen given the extent to which it's been used!)

我尝试使用 N 前缀，甚至使用test select语句，结果如下：

I've tried using the N prefix even using the test select statement, here are the results:

推荐答案

þ字符（扩展的ASCII {通过ISO-8859-1和ANSI代码第1252页}& UNICODE值为254）被称为刺。并且在某些语言中直接等于 th ：

The þ character (Extended ASCII { via ISO-8859-1 and ANSI Code Page 1252 } & UNICODE value of 254) is known as "thorn" and in some languages equates directly to th:

• 此处字符的技术信息：> http://unicode-table.com/en/00FE/

此处的字符和排序规则的解释： http：/ /userguide.icu-project.org/collat​​ion/customization 。在页面（通常为 Control-F ）中搜索复杂的裁缝示例。并且您将看到以下内容：

Explanation of that character and collations here: http://userguide.icu-project.org/collation/customization. Search the page — typically Control-F — for "Complex Tailoring Examples" and you will see the following:

UCA / root归类通常将字母þ（THORN）视为具有主要级别的单独字母在 z之后排序。但是，在瑞典语和其他斯堪的纳维亚语言中，和Þ应被视为与字母 th的三级差异。和 TH

The letter 'þ' (THORN) is normally treated by UCA/root collation as a separate letter that has primary-level sorting after 'z'. However, in Swedish and some other Scandinavian languages, 'þ' and 'Þ' should be treated as just a tertiary-level difference from the letters "th" and "TH" respectively.

如果您不想þ等于 th ，然后按如下所示强制执行二进制排序：

If you do not want þ to equate to th, then force a Binary collation as follows:

SELECT REPLACE(N'this is the news with a þ' COLLATE Latin1_General_100_BIN2,
                 N'þ', N'__');

返回值：

this is the news with a __

---

的新闻有关使用排序规则，Unicode，编码等，请访问：排序规则信息

这篇关于在T-SQL中替换Unicode字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！