0 votes
1 view
in SQL by (20.3k points)

How can we check in a SQL Server WHERE condition whether the column is not null and not the empty string ('')?

1 Answer

0 votes
by (40.4k points)

 1: If you just want to match " " as an empty string, then use the WHERE clause like this:

WHERE DATALENGTH(COLUMN) > 0 

2: But, if you want to count any string which consists of entire spaces as empty then use the WHERE clause like this :

WHERE COLUMN <> '' 

The above code will not return NULL values when they are used in a WHERE clause. And NULL will evaluate as UNKNOWN for these rather than the TRUE value.

CREATE TABLE T 

  ( 

     C VARCHAR(10) 

  ); 

INSERT INTO T 

VALUES      ('A'), 

            (''),

            ('    '), 

            (NULL); 

SELECT * 

FROM   T 

WHERE  C <> ''

The above code will return only the single row A. The rows that have the NULL or an empty string or a string consisting entirely of spaces are all excluded by this query.

Visit this SQL Certification to learn SQL Full Package. 

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...