Blank vs. Space

Hello, it seems to me that T-SQL is having a hard time
distinguishing a blank from one or more spaces. When I run
the statements below, the SELECTs always give 1
as a result. This seems completely wrong to me.. or am I
missing something?
CREATE TABLE TempTable (Value VARCHAR(10))
INSERT INTO TempTable (Value) VALUES ('')
SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
using Query Analyzer on SQL Server 2000 sp3a
Thanks,
SteveSQL Server does not take in mind spaces in the right side when using operato
r
= to do the comparison.
Example:
select 1 where space(0) = space(10)
go
try:
SELECT COUNT(*) FROM TempTable WHERE datalenght(Value) = 0
SELECT COUNT(*) FROM TempTable WHERE Value like ' '
SELECT COUNT(*) FROM TempTable WHERE Value like ' '
go
AMB
"Steve Deering" wrote:

> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||This is, I believe, according to the ANSI SQL definition. You can use LIKE i
nstead, as LIKE is
sensitive to trailing spaces:
SELECT COUNT(*) FROM TempTable WHERE Value LIKE '' --blank
SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --one space
SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --two spaces
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Deering" <SteveREMOVE@.vin.com> wrote in message
news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||Trailing spaces are dropped so this is working as advertised.
Try this and you'll see what I mean.
declare @.value varchar(10)
set @.value = ' '
SELECT len(@.value)
...returns 0
This:
set @.value = ' b '
SELECT len(@.value)
...returns 2, the leading space and 'b'
"Steve Deering" <SteveREMOVE@.vin.com> wrote in message
news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||Tibor,
I think it is right except for the first one.
Example:
select 1
from
(
select space(0) union all select space(1) union all select space(2)
) as t(colA)
where colA like space(0);
AMB
"Tibor Karaszi" wrote:

> This is, I believe, according to the ANSI SQL definition. You can use LIKE
instead, as LIKE is
> sensitive to trailing spaces:
>
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --two spaces
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Deering" <SteveREMOVE@.vin.com> wrote in message
> news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
>
>|||Indeed. Thanks Alejandro... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:CCBDF7B5-B5B2-459D-9DA8-339EFC53AA59@.microsoft.com...
> Tibor,
> I think it is right except for the first one.
> Example:
> select 1
> from
> (
> select space(0) union all select space(1) union all select space(2)
> ) as t(colA)
> where colA like space(0);
>
> AMB
> "Tibor Karaszi" wrote:
>|||Huh. Apparently, it's similar for JOINs, as if the values get RTRIM'd:
USE Northwind
GO
CREATE TABLE TempTable (Value VARCHAR(10))
GO
INSERT INTO TempTable (Value) VALUES ('abc') -- Insert a ZLS
INSERT INTO TempTable (Value) VALUES ('abc ')
INSERT INTO TempTable (Value) VALUES ('abc ')
GO
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc' -- ZLS
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc ' -- 1 space
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc ' -- 2 spaces
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc' + Space(5) -- 5 spaces
GO
SELECT * FROM TempTable t inner join
(select value from TempTable where Value='abc ') t1 on t.value = t1.value
WHERE t.Value = 'abc '
DROP TABLE TempTable
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
One of the other code monkeys here posed an interesting question, though we
can't devise an example: What if in some implementation, the trailing
spaces have significant meaning, so that "bob" should not match "bob "?
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||My name isn't "bob", it's "bob ".
Do you think that you can legally change your name to add a few trailing
spaces. :-)
When is a door not a door?
When it's a "door ".
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:euQJpVGQFHA.2252@.TK2MSFTNGP15.phx.gbl...
> One of the other code monkeys here posed an interesting question, though
> we
> can't devise an example: What if in some implementation, the trailing
> spaces have significant meaning, so that "bob" should not match "bob
> "?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||You can use LIKE operator in a join condition.
Example:
select
*
from
(
select 'Microsoft'
) as t1(colA)
inner join
(
select 'Microsoft' + space(1)
) as t2(colA)
on t1.colA like t2.colA
go
AMB
"Mike Labosh" wrote:

> One of the other code monkeys here posed an interesting question, though w
e
> can't devise an example: What if in some implementation, the trailing
> spaces have significant meaning, so that "bob" should not match "bob "
?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>