SQL Server 2000 - Filter by String Length

Question

Hello,

I have a database on a SQL Server 2000 server. This database has a table called "Person" that has a field call "FullName" that is a VARCHAR(100).

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

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

Thank you

Answer 1

This would be the preferred way I suppose

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

Answer 2

How about (ISNULL)

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

Answer 3

make your data consistent first:

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

then query your data without worrying about inconsistencies:

SELECT 
    *
    FROM Person
    WHERE FullName IS NOT NULL

IF you don't/can't to fix your data, you can use this:

SELECT 
    *
    FROM Person
    WHERE FullName != ''