Complete Guide to Checking for Not Null and Not Empty String in SQL Server

Handling NULL and Empty Strings in SQL Server

Properly handling NULL values and empty strings is crucial for ensuring data integrity and query accuracy in database development. SQL Server employs three-valued logic (TRUE, FALSE, UNKNOWN) for comparison operations, which requires special attention when dealing with NULL values.

Fundamental Concepts: NULL vs Empty String

NULL represents missing or unknown values, while an empty string ('') is a definite string value with zero length. In SQL Server, any comparison involving NULL (including comparison with itself) returns UNKNOWN rather than TRUE or FALSE.

-- Create test table and insert sample data
CREATE TABLE SampleTable 
(
    ColumnValue VARCHAR(10)
);

INSERT INTO SampleTable 
VALUES ('A'), 
       (''),
       ('    '), 
       (NULL);

Primary Methods for Checking Not NULL and Not Empty String

Method 1: Using Inequality Operator

The most straightforward approach uses the inequality operator to check that the column is not equal to an empty string:

SELECT * 
FROM SampleTable 
WHERE ColumnValue <> '';

This method automatically excludes NULL values because in the WHERE clause, any comparison with NULL returns UNKNOWN, and UNKNOWN is treated as FALSE in WHERE conditions. The query returns only the row with value 'A', excluding NULL, empty strings, and all-space strings.

Method 2: Using DATALENGTH Function

For more precise control over what constitutes "empty," use the DATALENGTH function:

SELECT * 
FROM SampleTable 
WHERE DATALENGTH(ColumnValue) > 0;

DATALENGTH returns the byte length of data, returning 0 for empty strings and NULL for NULL values. In the WHERE clause, DATALENGTH(ColumnValue) > 0 returns UNKNOWN for NULL values, thus excluding them.

Method 3: Using NULLIF Function

Another approach combines NULLIF with IS NOT NULL:

SELECT * 
FROM SampleTable 
WHERE NULLIF(ColumnValue, '') IS NOT NULL;

NULLIF(ColumnValue, '') returns NULL when ColumnValue equals '', otherwise it returns ColumnValue itself. Then IS NOT NULL ensures the result is not NULL.

Method Comparison and Performance Analysis

Semantic Differences

WHERE ColumnValue <> '' excludes all values equal to empty string, including all-space strings (since ' ' <> '' returns TRUE). WHERE DATALENGTH(ColumnValue) > 0 only excludes strings with actual zero length.

Performance Considerations

In most cases, WHERE ColumnValue <> '' offers the best performance as it can directly utilize indexes. DATALENGTH function calls might prevent index usage but remain useful when precise length control is needed.

Common Pitfalls and Best Practices

Avoiding OR Logic Errors

Beginners often mistakenly use OR instead of AND:

-- Incorrect example
DECLARE @user VARCHAR(30) = '';
IF (@user IS NOT NULL OR @user <> '')
BEGIN
    PRINT 'I am not empty';
END
ELSE
BEGIN
    PRINT 'I am empty';
END

Since empty string is not NULL, the first condition @user IS NOT NULL returns TRUE, causing incorrect output. The correct approach uses AND:

-- Correct example
IF (@user IS NOT NULL AND @user <> '')
BEGIN
    PRINT 'I am not empty';
END
ELSE
BEGIN
    PRINT 'I am empty';
END

Discussion on Using Greater Than Operator

Some discussions mention using WHERE ColumnValue > '', but this is not recommended because:

-- Not recommended method
DECLARE @test VARCHAR(30) = CHAR(15) + CHAR(14) + CHAR(16);
IF @test > ''
BEGIN
    SELECT 'I am not empty';
END
ELSE
BEGIN
    SELECT 'I am empty';
END

Certain special character combinations might be incorrectly identified as "less than" empty string, leading to logical errors.

Practical Application Scenarios

Data Validation and Cleaning

Ensuring critical fields are not empty during data import or user input validation:

-- Validate users with valid email addresses
SELECT UserName, Email
FROM Users
WHERE Email IS NOT NULL AND Email <> '';

Report Generation

Excluding incomplete records when generating reports:

-- Generate sales report with only orders having customer information
SELECT OrderID, CustomerName, TotalAmount
FROM Orders O
INNER JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE C.CustomerName IS NOT NULL AND C.CustomerName <> '';

Advanced Techniques and Optimization

Using COALESCE to Simplify Logic

For scenarios requiring default values, combine with COALESCE:

-- Display user information with "Not Provided" for empty values
SELECT 
    UserName,
    COALESCE(NULLIF(PhoneNumber, ''), 'Not Provided') AS DisplayPhone
FROM Users
WHERE PhoneNumber IS NOT NULL AND PhoneNumber <> '';

Application in Stored Procedures

Parameter validation in stored procedures:

CREATE PROCEDURE GetUserByEmail
    @Email VARCHAR(100)
AS
BEGIN
    IF @Email IS NOT NULL AND @Email <> ''
    BEGIN
        SELECT * FROM Users WHERE Email = @Email;
    END
    ELSE
    BEGIN
        RAISERROR('Email parameter cannot be empty', 16, 1);
    END
END

Summary and Recommendations

When checking if a column is neither NULL nor empty string in SQL Server, WHERE ColumnValue <> '' is recommended for its simplicity, efficiency, and readability. For cases requiring more precise control, consider DATALENGTH or NULLIF methods. Always remember SQL's three-valued logic characteristics and avoid common logical errors, particularly ensuring correct logical operators when combining multiple conditions.

In practical development, choose appropriate methods based on specific requirements and add adequate comments to enhance code readability. For performance-sensitive applications, test different methods with actual data distribution patterns.