Having Problems with a Simple Query

Hey guys,
I am trying to run a query that will return all rows where a column is
completely empty of data.
I try this query:
select * from patients_visitInsurers
where CompanyID is NULL
But it does not return anything for me. If I try the reverse
select * from patients_visitInsurers
where CompanyID is not NULL
It returns rows that contain CompanyId values and rows that are blank.
What am I missing here? Thanks!Do you have those blank values for CompanyID being NULL or just blank
string? Assuming CompanyID is character format, you can check:
SELECT <columns>
FROM patients_visitInsurers
WHERE COALESCE(CompanyID, '') = ''
Or
SELECT <columns>
FROM patients_visitInsurers
WHERE CompanyID IS NULL
OR CompanyID = ''
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Is CompanyID a string? "Empty of data" and "blank" are two different
things, in my opinion. Empty of data means NULL (and you have the correct
syntax, of that were the case). Blank means an empty string, e.g. ''. In
which case,
WHERE CompanyID = ''
You may also be safer to trim the data first, since it could contain a
space...
WHERE RTRIM(CompanyID) = ''
However, you should not insert a blank string when you really meant NULL.
They are different in implementation, and they are different on a conceptual
level, as well.
<alvinstraight38@.hotmail.com> wrote in message
news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
> Hey guys,
> I am trying to run a query that will return all rows where a column is
> completely empty of data.
> I try this query:
> select * from patients_visitInsurers
> where CompanyID is NULL
> But it does not return anything for me. If I try the reverse
> select * from patients_visitInsurers
> where CompanyID is not NULL
> It returns rows that contain CompanyId values and rows that are blank.
> What am I missing here? Thanks!|||Plamen Ratchev was thinking very hard :
> Do you have those blank values for CompanyID being NULL or just blank string?
> Assuming CompanyID is character format, you can check:
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE COALESCE(CompanyID, '') = ''
> Or
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE CompanyID IS NULL
> OR CompanyID = ''
>
and if that doesn't get it, then
OR LTRIM(RTRIM(CompanyID)) = ''
HTH,
Brad.|||On Apr 4, 11:07=A0am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Is CompanyID a string? =A0"Empty of data" and "blank" are two different
> things, in my opinion. =A0Empty of data means NULL (and you have the corre=ct
> syntax, of that were the case). =A0Blank means an empty string, e.g. ''. ==A0In
> which case,
> WHERE CompanyID =3D ''
> You may also be safer to trim the data first, since it could contain a
> space...
> WHERE RTRIM(CompanyID) =3D ''
> However, you should not insert a blank string when you really meant NULL.
> They are different in implementation, and they are different on a conceptu=al
> level, as well.
> <alvinstraigh...@.hotmail.com> wrote in message
> news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
>
> > Hey guys,
> > I am trying to run a query that will return all rows where a column is
> > completely empty of data.
> > I try this query:
> > select * from patients_visitInsurers
> > where CompanyID is =A0NULL
> > But it does not return anything for me. =A0If I try the reverse
> > select * from patients_visitInsurers
> > where CompanyID is =A0not NULL
> > It returns rows that contain CompanyId values and rows that are blank.
> > What am I missing here? =A0 Thanks!- Hide quoted text -
> - Show quoted text -
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows, but I want to exclude rows
that do contain values in this field.
Thanks!|||>>
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows,
Right. So you need to have two concepts very clear:
NULL is the absence of any data whatsoever.
'' is an empty string. It is data, even if it is zero-length.
There is little point in setting this column to NOT NULL if you can stick an
empty string in there. In this specific situation, it is apparent that the
two concepts are interchangeable...sql