像 SQL Server 中的运算符和尾随空格 [英] Like operator and Trailing spaces in SQL Server

查看:42
本文介绍了像 SQL Server 中的运算符和尾随空格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

这个匹配 column_name like 'CharEndsHere%'

这个不column_name 像'CharEndsHere'

我知道 like 运算符甚至会考虑尾随空格,所以我只是复制了精确列值(带有尾随空格)并粘贴了它.

I know that like operator will consider even the trailing spaces, so I just copied the exact column value (with trailing spaces) and pasted it.

类似于 column_name like 'CharEndsHere ' 但它不匹配 -- 为什么?.

Something like column_name like 'CharEndsHere ' yet it doesn't match -- why?.

我没有使用 '=' 运算符,因为列类型是 ntext

I haven't used '=' operator since the columns type is ntext

这里是否有我遗漏的东西,或者我不应该以这种方式使用 like 运算符?

Is there something I am missing here or shouldn't I use like operator in this way?

column_name like 'CharEndsHere__'(__ 表示空格)'CharEndsHere ' 是该单元格中的确切值,使用 like这种方式有效还是无效?

Edited : column_name like 'CharEndsHere__' (__ denoted the spaces) 'CharEndsHere ' is the exact value in that cell, using like in this way valid or no?

这是我试过的代码,

SELECT * 
FROM [DBName].[dbo].[TableName] 
WHERE [DBName].[dbo].[TableName].Address1 LIKE rtrim('4379 Susquehanna Trail S  ') 

我也试过不使用rtrim,结果还是一样

I have also tried without using rtrim, yet the same result

根据 Blindy 的回答,

According to Blindy's answer,

If a comparison in a query is to return all rows with the string LIKE 'abc' (abc
without a space), all rows that start with abc and have zero or more trailing 
blanks are returned.

但在我的情况下,我查询了 Like 'abc' 并且有一个包含 'abc '(带有尾随空格) 的单元格没有返回.这是我的实际问题

But in my case, I have queried, Like 'abc' and there is a cell containing 'abc '(with trailing spaces) which is not returned. That's my actual problem

推荐答案

这是一个阅读文档的案例,这里有非常明确的说明:http://msdn.microsoft.com/en-us/library/ms179859.aspx

This is a case of reading the documentation, it's very explicitly stated here: http://msdn.microsoft.com/en-us/library/ms179859.aspx

当您使用 LIKE 执行字符串比较时,模式字符串中的所有字符都是重要的.这包括前导或尾随空格.如果查询中的比较要返回带有字符串 LIKE 'abc '(abc 后跟一个空格)的所有行,则不会返回该列的值为 abc(没有空格的 abc)的行.但是,模式所匹配的表达式中的尾随空格将被忽略.如果查询中的比较是返回带有字符串 LIKE 'abc'(abc 不带空格)的所有行,则返回所有以 abc 开头并具有零个或多个尾随空格的行.

When you perform string comparisons by using LIKE, all characters in the pattern string are significant. This includes leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned.

根据您的评论,您似乎正在寻找一种使用 like 而忽略尾随空格的方法.使用类似这样的东西:field like rtrim('abc ').它仍然会使用索引,因为 rtrim() 是一个标量操作数,它在查找阶段之前被评估.

According to your comments, you seem to be looking for a way to use like while ignoring trailing spaces. Use something like this: field like rtrim('abc '). It will still use indexes because rtrim() is a scalar operand and it's evaluated before the lookup phase.

这篇关于像 SQL Server 中的运算符和尾随空格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆