sql查询非ascii字符_SQL替换：如何在SQL Server中替换ASCII特殊字符

sql查询非ascii字符

One of the important steps in an ETL process involves the transformation of source data. This could involve looking up foreign keys, converting values from one data type into another, or simply conducting data clean-ups by removing trailing and leading spaces. One aspect of transforming source data that could get complicated relates to the removal of ASCII special characters such as new line characters and the horizontal tab. In this article, we take a look at some of the issues you are likely to encounter when cleaning up source data that contains ASCII special characters and we also look at the user-defined function that could be applied to successfully remove such characters.

ETL过程中的重要步骤之一涉及源数据的转换。 这可能涉及查找外键，将值从一种数据类型转换为另一种数据类型，或仅通过删除尾部和前导空格来进行数据清除。 转换可能会变得复杂的源数据的一方面涉及到删除ASCII特殊字符，例如换行符和水平制表符。 在本文中，我们介绍了清理包含ASCII特殊字符的源数据时可能遇到的一些问题，还介绍了可成功用于删除此类字符的用户定义函数。

替换ASCII可打印字符 (Replacing ASCII Printable Characters)

The American Standard Code for Information Interchange (ASCII) is one of the generally accepted standardized numeric codes for representing character data in a computer. For instance, the ASCII numeric code associated with the backslash (\) character is 92. Many of the software vendors abide by ASCII and thus represents character codes according to the ASCII standard. Likewise, SQL Server, which uses ANSI – an improved version of ASCII, ships with a built-in CHAR function that can be used to convert an ASCII numerical code back to its original character code (or symbol). Script 1 shows us an example of how an ASCII numeric code 92 can be converted back into a backslash character as shown in Figure 1.

美国信息交换标准代码 （ASCII）是一种用于在计算机中表示字符数据的公认标准数字代码之一。 例如，与反斜杠（ \ ）字符关联的ASCII数字代码为92。许多软件供应商都遵守ASCII，因此根据ASCII标准表示字符代码。 同样，使用ANSI（一种ASCII的改进版本）SQL Server附带一个内置的CHAR函数，可用于将ASCII数字代码转换回其原始字符代码（或符号）。 脚本1为我们提供了一个示例，说明如何将ASCII数字代码92转换回反斜杠字符， 如图1所示。

SELECT CHAR(92);

The backslash character falls into a category of ASCII characters that is known as ASCII Printable Characters – which basically refers to characters visible to the human eye. Table 1 shows a top 5 sample of ASCII Printable Characters.

反斜杠字符属于ASCII字符类别，称为ASCII可打印字符 -基本上是指人眼可见的字符。 表1显示了ASCII可打印字符的前5个示例。

Numeric Code	Character	Description
33	!	Exclamation Mark
35	#	Number
36	$	Dollar
37	%	Percent
38	&	Ampersand

数字代码	字符	描述
33	！	感叹号
35	＃	数
36	$	美元
37	％	百分