Baffled

I have a table called lab_results_import, and one of the fields is called
qualifier_id. The table was empty, I imported 1 Excel spreadsheet with 150
0
records. 134 of these records have an empty cell in the Excel spreadsheet i
n
the qualifier_id column. The ss imports fine, but I am baffled by this quer
y
that I am running on the table. The query is
SELECT *, qualifier_id
FROM lab_results_import
WHERE (qualifier_id = '')
which returns 134 records, BUT
SELECT *, qualifier_id
FROM lab_results_import
WHERE (qualifier_id = ' ')
also returns 134 records, and regardless of how many spaces are in my
(qualifier_id = '') where clause, the query returns 134 records.
Anybody know what is going on, and/or how to search for a field containing a
certain number of spaces.
Thanks a lot.
ArcherWhat data type is this column (qualifier_id)? Is it nullable?
ML|||>> Anybody know what is going on, and/or how to search for a field
It is a known behavior where the trailing blanks of are being trimmed off
for comparison. The workaround is to use a character which is not in your
column like '~' or '*' like:
SELECT *
FROM tbl
WHERE '~' + col + '~' = '~' + SPACE( 10 ) + '~' ;
Note that such padding in general, will avoid the usage of any existing
indexes in the column, though.
Anith|||Try,
select
c1, datalength(c1)
from
(
select cast(space(0) as varchar(25))
union all
select space(1)
union all
select space(2)
union all
select space(3)
union all
select space(4)
) as t1(c1)
where
'.' + c1 + '.' = '.' + space(3) + '.'
-- or
select
c1, datalength(c1)
from
(
select cast(space(0) as varchar(25))
union all
select space(1)
union all
select space(2)
union all
select space(3)
union all
select space(4)
) as t1(c1)
where
c1 = ' ' and datalength(c1) = 3
go
Joe Celko explained one time why sql server seems to ignore trailing spaces
when comparing varchar data type, but I could not find it.
AMB
"bagman3rd" wrote:

> I have a table called lab_results_import, and one of the fields is called
> qualifier_id. The table was empty, I imported 1 Excel spreadsheet with 1
500
> records. 134 of these records have an empty cell in the Excel spreadsheet
in
> the qualifier_id column. The ss imports fine, but I am baffled by this qu
ery
> that I am running on the table. The query is
> SELECT *, qualifier_id
> FROM lab_results_import
> WHERE (qualifier_id = '')
>
> which returns 134 records, BUT
> SELECT *, qualifier_id
> FROM lab_results_import
> WHERE (qualifier_id = ' ')
> also returns 134 records, and regardless of how many spaces are in my
> (qualifier_id = '') where clause, the query returns 134 records.
> Anybody know what is going on, and/or how to search for a field containing
a
> certain number of spaces.
> Thanks a lot.
> Archer|||varchar(10)
and nulls are allowed. I actually want the cells to be null and I am trying
to avoid having cells with emtpy spaces.
A
"ML" wrote:

> What data type is this column (qualifier_id)? Is it nullable?
>
> ML