Hi:

I'm tearing my hair out with this!

I'm trying to do a simple SELECT:

select * from MyTable
WHERE MyField = 'This field value has spaces'

and it is coming up blank. If I look for any value without spaces, it
gives me records. The field is varchar(32), and I'm using SQL 2000 SP4.

Any ideas??

Thanks,
Kayda

[quoted text, click to view]

Why don't you append a character to the end of each string
i.e.
select * from MyTable
WHERE MyField + '@' = 'This field value has spaces' + '@'

--
David Rowland
Version 1.3 of DBMonitor is out now!
http://dbplussoftware.com

By default, varchar does not trim trailing spaces. You need to perform
ltrim(rtrim()) when the data is entered. You can play with Like and
other tricks. However, when you have a large table, you want to be
able to index the table as is.

Can you post the DDL and the DML so we can replicate the problem?

Not knowing if this was correct or not this is what I did:

CREATE TABLE TempSaiko1 (
SField1 VARCHAR(50),
SField2 VARCHAR(10)
)

INSERT INTO TempSaiko1 VALUES('Test Data',' ')

SELECT * FROM TempSaiko1 WHERE SField2 = ' '

SField1 SField2
-------------------------------------------------- ----------
Test Data

(1 row(s) affected)