How to Check If a SQL Server String Is Null or Empty

How to use NULL or empty string in SQL

Select *
From Table
Where (col is null or col = '')

Or

Select *
From Table
Where IsNull(col, '') = ''

How to check for Is not Null And Is not Empty string in SQL server?

If you only want to match "" as an empty string

WHERE DATALENGTH(COLUMN) > 0 

If you want to count any string consisting entirely of spaces as empty

WHERE COLUMN <> '' 

Both of these will not return NULL values when used in a WHERE clause. As NULL will evaluate as UNKNOWN for these rather than TRUE.

CREATE TABLE T 
  ( 
     C VARCHAR(10) 
  ); 

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

SELECT * 
FROM   T 
WHERE  C <> ''

Returns just the single row A. I.e. The rows with NULL or an empty string or a string consisting entirely of spaces are all excluded by this query.

SQL Fiddle

Null or empty check for a string variable

Yes, that code does exactly that.

You can also use:

if (@value is null or @value = '')

Edit:

With the added information that @value is an int value, you need instead:

if (@value is null)

An int value can never contain the value ''.

SQL Server: NULL or empty string?

What you have already stated yourself and the code you have given is correct.

Notably, the code from your first example,

`INNER JOIN myTable B ON ISNULL(B.someColumn, '') = ISNULL(A.someColumn, '')`

forces SQL server to scan the whole tables and to compute ISNULL(...) before being able to do the join. This means that it can't use any index to speed up the join, which will drastically decrease performance. The same procedure will be repeated when executing that query the next time, so you can't expect that subsequently running the query again will be faster.

[ For the record, of course you could make ISNULL(...) a computed column, index that and use it in the joins, but that seems a little exaggerated given the other possible solutions. ]

Whether you should use '' instead of NULL or rather make another approach depends of the effort the former requires:

If your application touches that column only if it wants to write a meaningful (read: non-null) value there, then you could solve the problem in three easy steps (update the column and turn all NULL values into ''; make the column non-nullable; change the column's default value from NULL to '').

But if your application touches that column in every case and actively writes NULL to it if appropriate, you would have to change the application itself to take that route. Whether changing the application is possible is known only to you ...

If you can't change the application, your third approach is good and works reliably:

(B.someColumn = A.someColumn) or (B.someColumn is NULL and A.someColumn is NULL)

It wouldn't disturb me that it is "wordy". The length of code in general does not mean anything with respect to performance, and in this case, the wordy code enables you to leave things as-is in your application (except this query), while enabling SQL server to use indexes to speed up the join.

In summary:

Use '' instead of NULL if your application allows that easily, or can be changed easily to allow it. Otherwise, use your third approach.

A final note regarding ANSI_NULLS:

Walter Vehoeven's comment below your question is correct, but in your case, ANSI_NULLS would not change anything. From the documentation (first paragraph below the table, formatting mine):

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

The second sentence means that it won't have any effect in your case, because you are comparing / joining two columns (even if it actually is the same column on both sides of the comparison).

How to check for null/empty/whitespace values with a single test?

Functionally, you should be able to use

SELECT column_name
  FROM table_name
 WHERE TRIM(column_name) IS NULL

The problem there is that an index on COLUMN_NAME would not be used. You would need to have a function-based index on TRIM(column_name) if that is a selective condition.

How to check if field is NULL and blank?

Is this what you are after

CREATE VIEW Lookup4 AS
SELECT TOP 140000 CONCAT(no,
    CASE WHEN (ISNULL(importantField,'') = '') 
        THEN '' ELSE ' ' + importantField END, 
            fieldname + ' ', anotherField2)  AS UNRELATEDFIELD, Code, 
    CASE NAME
        WHEN '101,,,,,' THEN 'value1e'
        WHEN '14,,,,,' THEN 'value3'
        WHEN '16,,,,,' THEN 'value4'
    END AS NAME
FROM  dbo.Lookup

SQL statement to check for empty string - T-SQL

I found a blog, https://bbzippo.wordpress.com/2013/09/10/sql-server-collations-and-string-comparison-issues/

which explained that

The problem is because the “default” collation setting
(SQL_Latin1_General_CP1_CI_AS) for SQL Server cannot properly compare
Unicode strings that contain so called Supplementary Characters
(4-byte characters).

A fix is to use a collation that doesn't have problems with the supplementary characters. For example:

select case when N'㴆' COLLATE Latin1_General_100_CI_AS_KS_WS = N'' then 1 else 0 end;

will return 0. See the blog for more examples.

Since you are comparing to the empty string, another solution would be to test the string length.

declare @str1 nvarchar(max) =N'㴆';
select case when len(@str1) = 0 then 1 else 0 end;

This will return 0 as expected.

This also yields 0 when the string is null.

EDIT:

Thanks to devio's comment, I dug a bit deeper and found a comment from Erland Sommarskog https://groups.google.com/forum/#!topic/microsoft.public.sqlserver.server/X8UhQaP9KF0

that in addition to not supporting Supplementary Characters, the Latin1_General_CP1_CI_AS collation doesn't handle new Unicode characters correctly. So I'm guessing that the 㴆 character is a new Unicode character.

Specifying the collation Latin1_General_100_CI_AS will also fix this issue.

How do check if a parameter is empty or null in Sql Server stored procedure in IF statement?

that is the right behavior.

if you set @item1 to a value the below expression will be true

IF (@item1 IS NOT NULL) OR (LEN(@item1) > 0)

Anyway in SQL Server there is not a such function but you can create your own:

CREATE FUNCTION dbo.IsNullOrEmpty(@x varchar(max)) returns bit as
BEGIN
IF @SomeVarcharParm IS NOT NULL AND LEN(@SomeVarcharParm) > 0
    RETURN 0
ELSE
    RETURN 1
END

---

Related Topics