Prev: Correct Way to Insert into Multiple Tables - Materialised
Next: How to select specific line in select statement from a give multiline text field/column of a table
From: tshad on 16 Jul 2008 12:42
I just ran into an issue in 2005 where if I have a field that is a char(8),
the select statement will still find the values if they are less then 8
characters (even though chars blank fill).

For example if Quarter is char(8), I can do:

SELECT *
FROM COURSE
WHERE Quarter = 'Fall'

and get all my records.

I believe in SQL 2000 this would find 0 records. I would have to do
something like:

SELECT *
FROM COURSE
WHERE TRIM(Quarter) = 'Fall'

I know I always had to test for ' ' instead of '' when I was looking for
state fields that were blank when I had state defined as char(2).

Has this changed?

Thanks,

Tom


From: Plamen on 16 Jul 2008 13:48
It is the same in SQL Server 2000 and SQL Server 2005. SQL Server uses
the ANSI rule for padding character stings when comparing. The
exception to this is the LIKE predicate. See the following article:

http://support.microsoft.com/kb/316626

HTH,

Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 16 Jul 2008 16:11
That is probably true.

But I remember when I was using Sql Server 2000, I had my States field set
to char(2) and did a query such as:

SELECT *
FROM Companies
WHERE State = ''

I wouldn't get any rows back but if I did:

SELECT *
FROM Companies
WHERE State = ' '

I would get rows back for any fields that were not null but had empty
strings in them.

Not sure why.

Thanks,

Tom

<Plamen(a)sqlstudio.com> wrote in message
news:23a9a062-1fac-46a6-803d-4ccc023e4554(a)k13g2000hse.googlegroups.com...
> It is the same in SQL Server 2000 and SQL Server 2005. SQL Server uses
> the ANSI rule for padding character stings when comparing. The
> exception to this is the LIKE predicate. See the following article:
>
> http://support.microsoft.com/kb/316626
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen on 16 Jul 2008 16:37
Here is a quick test on SQL Server 2000:

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol CHAR(2));

INSERT INTO Foo VALUES(1, '');
INSERT INTO Foo VALUES(2, ' ');

SELECT keycol, datacol
FROM Foo
WHERE datacol = '';


SELECT keycol, datacol
FROM Foo
WHERE datacol = ' ';

SELECT @@VERSION;

Results:

keycol datacol
----------- -------
1
2

(2 row(s) affected)

keycol datacol
----------- -------
1
2

(2 row(s) affected)




Microsoft SQL Server 2000 - 8.00.194
(Intel
X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


(1 row(s) affected)


HTH,

Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Correct Way to Insert into Multiple Tables - Materialised
Next: How to select specific line in select statement from a give multiline text field/column of a table