Does SQL Server can not detect number of space in field value?

[Xie Bingpeng]

hi all:
Suppose I have a table called Customer, which has two fields: CustID and
Name,
now post three records:

insert customer values('001','') --name contains no space
insert customer values('002',' ') -- name contains 1 space
insert customer values('003',' ') -- name contains 2 space

now I need return the second record(contains 1 space):
select * from customer where name =' '
but SQL Server return all three records.
that is: SQL Server can not detect number of space in field value.
how to resolve this problem?

thanks.

[Ignacio Vazquez]

Xie Bingpeng wrote:

> that is: SQL Server can not detect number of space in field value.
> how to resolve this problem?

Define the field as varbinary instead of varchar.

--
Cheers,
Ignacio

[a]

Xie,

By default MS SQL removes trailing spaces from VarChar() fields.

There is a global system option to surpress this, but it affects all
databases on the server.

Thanks,

Brett

"Xie Bingpeng" <x...@teabird.com.cn> wrote in message
news:4004b8a1$1...@newsgroups.borland.com...

[Xie Bingpeng]

thanks for reply.
what global system option to surpress this?

"a" <blwa...@shaw.ca> wrote in message
news:4004cebd$1...@newsgroups.borland.com...

[Brett Watters]

Xie,

On MS SQL 2000:

1) Go into your SQL Enterprise Manager.
2) Select 'Microsoft SQL Server' in the tree and edit its properties.
3) Go to the "Connections" tab panel.
4) Turn on the allow "ANSI padding" in the list.
5) Press Ok.

Again, this is a per server setting and affects all databases on the
machine. You can't expect a client to necessarily make this setting
on their servers for your application to work -- as you will likely
break other applications using other databases on the server.

Thanks,

Brett

"Xie Bingpeng" <x...@teabird.com.cn> wrote in message

news:40051419$1...@newsgroups.borland.com...