CODEWITHSUNDEEP
sqlsql-server
Nick Matveev
Nick Matveev

Reputation: 5

How to treat NULLs like '' (empty data) in comparison WHERE statements

I know that if you try to compare a NULL value to something else. It being NULL makes the whole statement NULL.

table.ColumnA = NULL
table.ColumnB = 'something'

If I do something like

where table.ColumnA <> table.ColumnB

It will not work.

I know you can do something like,

(NOT (a <> b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL))

But my query is already pretty long and complicated. Trying to add this routine for every column would be a nightmare.

I was hoping there was an easier way to just temporarily treat NULL as ''

Upvotes: 0

Views: 101

Answers (2)

Harry Clark
Harry Clark

Reputation: 98

Be careful blanket removing nulls, but if you need a quick solution to treat NULL as an empty string while preserving non-nulls you can use the coalesce function:

COALESCE(columnname, '')

Docs here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

The logic for a <> b with NULL values is:

where (a <> b or a is null and b is not null or a is not null and b is null)

Unfortunately, SQL Server doesn't support the standard is distinct from clause, which would make this much simpler.

If there are values that you know will never be used, you can use coalesce():

where coalesce(a, '<null>') <> coalesce(b, '<null>')

Upvotes: 1

Related Questions