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
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, '') = ''
Compatible SQL to test for not null and not empty strings
NULLIF
is available on both Oracle (doc) and SQL Server (doc). This expression should work:
NULLIF(column, '') IS NOT NULL
In both servers, if column
is NULL
, then the output of NULLIF
will just pass the NULL
value through. On SQL Server, '' = ''
, so the output of NULLIF
will be NULL
. On Oracle, ''
is already NULL
, so it gets passed through.
This is my test on SQL Server 2008 R2 Express:
WITH SampleData AS
(SELECT 1 AS col1, NULL AS col2
UNION ALL
SELECT 2, ''
UNION ALL
SELECT 3, 'hello')
SELECT *
FROM SampleData
WHERE NULLIF(col2, '') IS NOT NULL;
And this is my test case on Oracle 10g XE:
WITH SampleData AS
(SELECT 1 AS col1, NULL AS col2 FROM DUAL
UNION ALL
SELECT 2, '' FROM DUAL
UNION ALL
SELECT 3, 'hello' FROM DUAL)
SELECT *
FROM SampleData
WHERE NULLIF(col2, '') IS NOT NULL;
Both return 3
as expected.
Best way to check for empty or null value
The expression stringexpression = ''
yields:
TRUE
.. for ''
(or for any string consisting of only spaces with the data type char(n)
)NULL
.. for NULL
FALSE
.. for anything else
So to check for: "stringexpression
is either NULL or empty":
(stringexpression = '') IS NOT FALSE
Or the reverse approach (may be easier to read):
(stringexpression <> '') IS NOT TRUE
Works for any character type including char(n)
. The manual about comparison operators.
Or use your original expression without trim()
, which is costly noise for char(n)
(see below), or incorrect for other character types: strings consisting of only spaces would pass as empty string.
coalesce(stringexpression, '') = ''
But the expressions at the top are faster.
Asserting the opposite is even simpler: "stringexpression
is neither NULL nor empty":
stringexpression <> ''
About char(n)
This is about the data type char(n)
, short for: character(n)
. (char
/ character
are short for char(1)
/ character(1)
.) Its use is discouraged in Postgres:
In most situations
text
orcharacter varying
should be used instead.
Do not confuse char(n)
with other, useful, character types varchar(n)
, varchar
, text
or "char"
(with double-quotes).
In char(n)
an empty string is not different from any other string consisting of only spaces. All of these are folded to n spaces in char(n)
per definition of the type. It follows logically that the above expressions work for char(n)
as well - just as much as these (which wouldn't work for other character types):
coalesce(stringexpression, ' ') = ' '
coalesce(stringexpression, '') = ' '
Demo
Empty string equals any string of spaces when cast to char(n)
:
SELECT ''::char(5) = ''::char(5) AS eq1
, ''::char(5) = ' '::char(5) AS eq2
, ''::char(5) = ' '::char(5) AS eq3;
Result:
eq1 | eq2 | eq3
----+-----+----
t | t | t
Test for "null or empty string" with char(n)
:
SELECT stringexpression
, stringexpression = '' AS base_test
, (stringexpression = '') IS NOT FALSE AS test1
, (stringexpression <> '') IS NOT TRUE AS test2
, coalesce(stringexpression, '') = '' AS coalesce1
, coalesce(stringexpression, ' ') = ' ' AS coalesce2
, coalesce(stringexpression, '') = ' ' AS coalesce3
FROM (
VALUES
('foo'::char(5))
, ('')
, (' ') -- not different from '' in char(n)
, (NULL)
) sub(stringexpression);
Result:
stringexpression | base_test | test1 | test2 | coalesce1 | coalesce2 | coalesce3
------------------+-----------+-------+-------+-----------+-----------+-----------
foo | f | f | f | f | f | f
| t | t | t | t | t | t
| t | t | t | t | t | t
null | null | t | t | t | t | t
Test for "null or empty string" with text
:
SELECT stringexpression
, stringexpression = '' AS base_test
, (stringexpression = '') IS NOT FALSE AS test1
, (stringexpression <> '') IS NOT TRUE AS test2
, coalesce(stringexpression, '') = '' AS coalesce1
, coalesce(stringexpression, ' ') = ' ' AS coalesce2
, coalesce(stringexpression, '') = ' ' AS coalesce3
FROM (
VALUES
('foo'::text)
, ('')
, (' ') -- different from '' in a sane character types
, (NULL)
) sub(stringexpression);
Result:
stringexpression | base_test | test1 | test2 | coalesce1 | coalesce2 | coalesce3
------------------+-----------+-------+-------+-----------+-----------+-----------
foo | f | f | f | f | f | f
| t | t | t | t | f | f
| f | f | f | f | f | f
null | null | t | t | t | t | f
db<>fiddle here
Old sqlfiddle
Related:
- Any downsides of using data type "text" for storing strings?
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.
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).
Comparing empty string with null value - SQL Server
Your first example returns fail because you have the wrong operator. If you want to see if something equals something else you use =
, not !=
Here is the code that proves that NULL
can be compared to ''
:
DECLARE @EmptyString VARCHAR(20) = '',
@Null VARCHAR(20) = Null;
SELECT
CASE WHEN ISNULL(@EmptyString, '') = ISNULL(@Null, '')
THEN 'Pass' ELSE 'Fail'
END AS EmptyStringVsNull
It returns pass because you use =
, not !=
How do I check if a column is empty or null in MySQL?
This will select all rows where some_col
is NULL
or ''
(empty string)
SELECT * FROM table WHERE some_col IS NULL OR some_col = '';
How do I check if a SQL Server text column is empty?
where datalength(mytextfield)=0
Related Topics
How to Connect to SQL Server 2005 Database Through Ruby
Why am I Able to Call the Class Method as If It Were an Instance Method Here
Standard SQL Boolean Operator Is VS. Equals (=) Operator
Replacing Null and Empty String Within Select Statement
SQL Server: Replace Invalid Xml Characters from a Varchar(Max) Field
SQL Pulling a Row for Next or Previous Row of a Current Row
Create a Delimitted String from a Query in Db2
What Is "Connect Timeout" in SQL Server Connection String
How to Combine Results of Two Queries into a Single Dataset
Inner Join VS Multiple Table Names in "From"
Writing a Subquery Using Zend Db
SQL Combine Two Columns in Select Statement
How to Compare Software Versions Using SQL Server
Why No Output When Plsql Anonymous Block Completes
How to Create Daylight Savings Time Start and End Function in SQL Server
How to Generate Date Series to Occupy Absent Dates in Google Biqquery
How to Remove White Space Characters from a String in SQL Server