S***k
发帖数: 370 | 1 sql 2005
一个column nvarchar(50), 里面的数据：
2007
2008
2007 and 2008
...
select *
from table
where column = '2007'
return 1100 rows
select *
from table
where column like '2007'
return 900 rows
column = '2007' 和 column like '2007' 有什么区别？ |
n********6
发帖数: 1511 | 2 Some examples.
TABLEA.COLUMNA
'20070101'
@DATA LIKE '2007%'
@DATA = SUBSTRING('20070101', 1, 4)
TABLEB.COLUMNB
'12132007'
LIKE '____2007' *4 underscores
LIKE '%2007'
More
TABLEC.COLUMNC
'03/16/2007'
... |
B*****g
发帖数: 34098 | 3 can you post result for
select *
from table
where column = '2007'
except
select *
from table
where column like '2007'
【在 S***k 的大作中提到】
: sql 2005
: 一个column nvarchar(50), 里面的数据：
: 2007
: 2008
: 2007 and 2008
: ...
: select *
: from table
: where column = '2007'
: return 1100 rows
|
S***k
发帖数: 370 | 4 The column was designed to hold customized input from users and not
specific to hold date time string.
Select *
From table
Where col = ‘2007’
Except
Select *
From table
Where col like ‘2007’
The number of returned rows is the difference between two queries. And all
of these records have 2007 in that column. |
B*****g
发帖数: 34098 | 5 ???
can you just post some data from the sql with except?
【在 S***k 的大作中提到】
: The column was designed to hold customized input from users and not
: specific to hold date time string.
:
: Select *
: From table
: Where col = ‘2007’
: Except
: Select *
: From table
: Where col like ‘2007’
|
c**t
发帖数: 2744 | 6 The difference is likely to be in trailing space.
The = op ignores trailing space, but like does NOT.
【在 S***k 的大作中提到】
: sql 2005
: 一个column nvarchar(50), 里面的数据：
: 2007
: 2008
: 2007 and 2008
: ...
: select *
: from table
: where column = '2007'
: return 1100 rows
|
a*******t
发帖数: 891 | 7 select *
from table
where column = '2007'
and not in
(select *
from table
where column like '2007')
see what's the difference.
【在 S***k 的大作中提到】
: sql 2005
: 一个column nvarchar(50), 里面的数据：
: 2007
: 2008
: 2007 and 2008
: ...
: select *
: from table
: where column = '2007'
: return 1100 rows
|
B*****g
发帖数: 34098 | 8 how can = ignores trailing space?
【在 c**t 的大作中提到】
: The difference is likely to be in trailing space.
: The = op ignores trailing space, but like does NOT.
|
c**t
发帖数: 2744 | 9 select '<' + col + '>' ... |
c**t
发帖数: 2744 | 10 http://support.microsoft.com/kb/316626
SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, <
Comparison Predicate>, 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 WHERE and HAVING clause predicates and
other Transact-SQL string comparisons. For example, Transact-SQL considers
the strings 'abc' an
【在 B*****g 的大作中提到】
: how can = ignores trailing space?
|
|
|
B*****g
发帖数: 34098 | 11 ding
in
padding
operations.
【在 c**t 的大作中提到】
: http://support.microsoft.com/kb/316626
: SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, <
: Comparison Predicate>, 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 WHERE and HAVING clause predicates and
: other Transact-SQL string comparisons. For example, Transact-SQL considers
: the strings 'abc' an
|
S***k
发帖数: 370 | 12 It is caused by the trailing space.
I used len(col) to check the length. It seems len() also ignore the trailing
space.
Thanks, guys. |
j*****n
发帖数: 1781 | 13 nice call!
in
padding
operations.
【在 c**t 的大作中提到】
: http://support.microsoft.com/kb/316626
: SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, <
: Comparison Predicate>, 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 WHERE and HAVING clause predicates and
: other Transact-SQL string comparisons. For example, Transact-SQL considers
: the strings 'abc' an
|
c**t
发帖数: 2744 | 14 居然没有包子，呵呵
【在 j*****n 的大作中提到】
: nice call!
:
: in
: padding
: operations.
|
S***k
发帖数: 370 | |
