Empty Field

[gust1480]

How can I query out the empty field in may database. I tried using "select * from table where field = '' " But It doesn't work! I'm still returned with a field with no value in it. And I'm pretty sure the field with no value in it is not null.

 

[rajeevnandanmishra]

Hi gust1480

If it is a not null field then it would be storing spaces depending on the datatype char/varchar. It also depends on a few ansi settings.
Following will help you in most of the cases:
------------
select * from table
where datalength(isnull(rtrim(myField),' ')) = ' '
------------

Raj

 

[gust1480]

Thanks for the response Raj. I tried it but it doesn't seem to work?! Is datelength a function? or a varibale?

 

[ClaireHCM]

Try this

select * from table where replace(field,' ','') =''

 

[rajeevnandanmishra]

gust1480,

Ooops! Sorry.

First i was writing something and then end up with something else.

just the line without datalength function will work.
i.e.
select * from table
where (isnull(rtrim(myField),' ')) = ' '


But i feel that claire has given a better idea.
Thanks claire. I have not thought of using replace for this comparison.


Raj

 

[gust1480]

Thanks guys for answering my query. But it still doesn't work Raj and so is the replace Claire. The empty field contains 30 spaces (the data type of the field is a Char and has a length of 30).

 

[Rhys666]

Does the empty field contain 30 space characters or is it null...
If T-SQL try...
<CODE>
SELECT DISTINCT
*
FROM
Table
WHERE
MyField IS NULL
OR
LTRIM(RTRIM(MyField)) = ''
GO
</CODE>
...to look for both, or take out one or other of the CLAUSES.

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opiniion!

 

[ClaireHCM]

I am interested to see your original datas?

If it's not null,neither ''.So what it will be??

Could you provide some data which belongs to the field you are confusing!

 

[gust1480]

HI Rhys, Thanks for answering,I tried what you said and it still doesn't work. Claire and Rhys, the fields is not null nor '', I think the field contains 30 spaces. I was able to determine this by retrieving the field in vb and using the lenght function (LEN) and it gave me 30. So the fields contains 30 spaces or whatever but it's definetely not null.

 

[ClaireHCM]

If it contains 30 space,after replace the 30 space to 0 space,I believe my code shoudl work.

Could you show me your coding and your data?