SQL Server 2000 - Filter by String Length

Question

I have a database on SQL Server 2000. In this database, there is a "Person" table that has a field call "FullName", which is VARCHAR (100).

I am trying to write a query that will allow me to get all records that have a name. Entries that do not have a name have a FullName value of either null or an empty string. How to get all Person entries with full name? In other words, I want to ignore entries that do not have a full name. I am currently trying to do the following:

SELECT * FROM Person p WHERE p.FullName IS NOT NULL AND LEN(p.FullName) > 0 

thanks

Answer 1

make your data consistent first :

 UPDATE Person SET FullName=NULL WHERE FullName != '' 

then request your details without worrying about inconsistencies:

 SELECT * FROM Person WHERE FullName IS NOT NULL 

IF you cannot / cannot correct your data, you can use this:

 SELECT * FROM Person WHERE FullName != '' 

Answer 2

That would be the preferred way, I suppose.

 SELECT * FROM Person p WHERE p.FullName <> '' 

Answer 3

How about ( ISNULL )

 SELECT * FROM Person p WHERE ISNULL(p.FullName,'') <> ''