在SQL Server(查询分析器)的查询结果中识别隐藏字符的最佳方法是什么? [英] What's the best way to identify hidden characters in the result of a query in SQL Server (Query Analyzer)?

问题描述

当试图识别错误的数据时(通常需要手动查看和删除)，我想要一种简单的方法来查看隐藏的字符，例如TAB，空格，回车符和换行符. 有内置的方法吗?

When trying to identify erroneous data (often needing manual review and removal), I'd like an easy way of seeing hidden characters, such as TAB, Space, Carriage return and Line feed. Is there a built-in way for this?

在关于Oracle的stackoverflow上类似的问题中，有人建议使用DUMP(fieldname)函数，但是我不知道这是否会使事情变得容易，即使SQL Server中存在相应的函数也是如此，因为我需要在角色的上下文中查看.

In a similar question here on stackoverflow, regarding Oracle, a DUMP(fieldname) function was suggested, but I don't know if that woud make things easier even if a corresponding function would exist in SQL Server, since I need to see the Characters in their context.

我能想到的最好的主意是用可见的字符替换预期的隐藏字符，如下所示:

The best idea I could come up with was replacing the expected hidden characters with visible ones, like this:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(myfield, ' ', '˙'), CHAR(13), '[CR]'), CHAR(10), '[LF]'), CHAR(9), '[TAB]') FROM mytable

有更好的方法吗?我不喜欢这种方式，因为可能还有其他一些较不常见的隐藏字符，例如垂直TAB等，这些不被我忽略....像在几乎所有文本编辑器中一样，打开显示隐藏字符"，在SQL Server查询分析器中将是一个很好的功能，所以我几乎期望它也可以在SQL Server中以某种方式完成...或者至少有人比我有一个更好的主意来显示这种空白信息.

Is there a better way? I don't like this way since there might be other less common hidden characters that are not taken into account by me such as vertical TAB etc... Turning on "show hidden characters", as you can do in almost any text editor, would be such a nice feature in SQL Server Query Analyzer, so I almost expect that it can be done somehow in SQL server as well... or at least that someone has an even better idea than mine, to show this kind of white space info.

我刚刚注意到，有一种内置的方式来查看空白"，而不是在SQL Query Analyzer中，而是在曾经是SQL Enterprise管理器的界面部分.右键单击"SQL Management Studio对象资源管理器"树中的表，然后选择编辑前200行".结果是空白区域(至少CR LF)显示为空白方块.

推荐答案

创建一个解决所有空白可能性并仅启用那些看起来合适的函数的函数:

Create a function that addresses all the whitespace possibilites and enable only those that seem appropriate:

SELECT dbo.ShowWhiteSpace(myfield) from mytable

仅取消注释要测试的空白情况:

Uncomment only those whitespace cases you want to test for:

CREATE FUNCTION dbo.ShowWhiteSpace (@str varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
     DECLARE @ShowWhiteSpace varchar(8000);
     SET @ShowWhiteSpace = @str
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(32), '[?]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(13), '[CR]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(10), '[LF]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(9),  '[TAB]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(1),  '[SOH]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(2),  '[STX]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(3),  '[ETX]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(4),  '[EOT]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(5),  '[ENQ]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(6),  '[ACK]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(7),  '[BEL]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(8),  '[BS]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(11), '[VT]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(12), '[FF]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(14), '[SO]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(15), '[SI]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(16), '[DLE]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(17), '[DC1]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(18), '[DC2]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(19), '[DC3]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(20), '[DC4]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(21), '[NAK]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(22), '[SYN]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(23), '[ETB]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(24), '[CAN]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(25), '[EM]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(26), '[SUB]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(27), '[ESC]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(28), '[FS]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(29), '[GS]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(30), '[RS]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(31), '[US]')
     RETURN(@ShowWhiteSpace)
END

这篇关于在SQL Server(查询分析器)的查询结果中识别隐藏字符的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！