Sql Server - 在 where 子句中执行 RTRIM/LTRIM 的替代方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10112301/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql Server - Alternative of doing a RTRIM/LTRIM in the where clause
提问by abbas
I hava a column name which is a varchar
我有一个列名，它是一个 varchar
I want to filter all results where name is an empty string...
我想过滤所有名称为空字符串的结果...
select name
from tblNames
where name <> ''
What I want to do is:
我想做的是：
select name
from tblNames
where Ltrim(RTrim(name)) <> ''
I want to apply a trim on name in the where clause but I have read a few articles mentioning the performance issue of functions inside the where clause
我想在 where 子句中对名称应用修剪，但我已经阅读了一些提到 where 子句中函数的性能问题的文章
I want a solution to this without hurting performance
我想要一个不影响性能的解决方案
回答by ypercube??
Standard behaviour in SQL-Server is that
SQL-Server 中的标准行为是
' ' = ''
is TRUE, because trailing spaces are ignored. From MSDN support:
is TRUE，因为尾随空格被忽略。来自MSDN 支持：
SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) 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. The padding directly affects the semantics of
WHEREandHAVINGclause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings'abc'and'abc 'to be equivalentfor most comparison operations.The only exception to this rule is the
LIKEpredicate. ...
SQL Server 遵循有关如何比较字符串与空格的 ANSI/ISO SQL-92 规范（第 8.2 节，一般规则 #3）。ANSI 标准要求对比较中使用的字符串进行填充，以便在比较它们之前它们的长度匹配。填充直接影响
WHERE和HAVING子句谓词和其他 Transact-SQL 字符串比较的语义。例如，Transact-SQL 认为字符串'abc'和对于大多数比较操作'abc '是等效的。此规则的唯一例外是
LIKE谓词。...
So, your condition WHERE name <> ''should work fine, and not include any strings where there are only spaces.
因此，您的条件WHERE name <> ''应该可以正常工作，并且不包含任何只有空格的字符串。
回答by johnnycrash
You could make a constraint that only trimmed data goes in the field.
您可以设置一个约束，只允许修剪的数据进入该字段。
You could make an index on LTRIM(RTRIM(name)). SQL Might be smart enough to use it.
你可以在 上做一个索引LTRIM(RTRIM(name))。SQL 可能足够聪明来使用它。
You could make a calculated field that is LTRIM(RTRIM(name)), index that field, and then use that field in your query.
您可以创建一个计算字段，即LTRIM(RTRIM(name))，索引该字段，然后在查询中使用该字段。
回答by Imre
While
尽管
'abc' = 'abc '
'abc' = 'abc '
(with spaces after the string on the right hand side of the equasion) is TRUE
（方程右侧的字符串后有空格）是 TRUE
'abc' = ' abc'
'abc' = ' abc'
(with spaces before the string on the right hand side of the equasion) is FALSE.
（方程右侧的字符串前有空格）是FALSE.
Therefore what is automatically ignored is the trailing spaces only (works like RTRIMbut not like LTRIM).
因此，自动忽略的只是尾随空格（像RTRIM但不像LTRIM）。
