如何让 SQL Server 返回 FALSE 以比较带有和不带有尾随空格的 varchars? [英] How can I make SQL Server return FALSE for comparing varchars with and without trailing spaces?
问题描述
如果我故意在 VARCHAR 列中存储尾随空格，如何强制 SQL Server 将数据视为不匹配?
If I deliberately store trailing spaces in a VARCHAR column, how can I force SQL Server to see the data as mismatch?
SELECT 'foo' WHERE 'bar' = 'bar '
我试过了:
SELECT 'foo' WHERE LEN('bar') = LEN('bar ')
我见过的一种浮动方法是在每个字符串的末尾附加一个特定字符，然后将其删除以供我演示......但这似乎很愚蠢.
One method I've seen floated is to append a specific character to the end of every string then strip it back out for my presentation... but this seems pretty silly.
有没有我忽略的方法?
我注意到它不适用于 leading 空格，所以也许我运行了一个函数，它在比较之前反转字符顺序.... 问题是这使查询变得不可SARGable....
I've noticed that it does not apply to leading spaces so perhaps I run a function which inverts the character order before the compare.... problem is that this makes the query unSARGable....
推荐答案
返回指定字符串表达式的字符数，不包括尾随空格.要返回用于表示表达式的字节数，请使用 DATALENGTH 函数
Returns the number of characters of the specified string expression, excluding trailing blanks. To return the number of bytes used to represent an expression, use the
DATALENGTHfunction
此外，来自 SQL Server 如何比较字符串与尾随空格:
Also, from the support page on How SQL Server Compares Strings with Trailing Spaces:
SQL Server 遵循有关如何将字符串与空格进行比较的 ANSI/ISO SQL-92 规范.ANSI 标准要求对比较中使用的字符串进行填充，以便在比较之前它们的长度匹配.
SQL Server follows the ANSI/ISO SQL-92 specification on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them.
更新:我使用 LIKE(在比较期间不填充空格)和 DATALENGTH() 删除了我的代码，因为它们比较字符串并非万无一失
Update: I deleted my code using LIKE (which does not pad spaces during comparison) and DATALENGTH() since they are not foolproof for comparing strings
在很多其他地方以及其他解决方案中也有人问过这个问题:
This has also been asked in a lot of other places as well for other solutions:
这篇关于如何让 SQL Server 返回 FALSE 以比较带有和不带有尾随空格的 varchars?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
