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
Answer from Martin Smith on Stack OverflowIf 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
WHERE NULLIF(your_column, '') IS NOT NULL
Nowadays (4.5 years on), to make it easier for a human to read, I would just use
WHERE your_column <> ''
While there is a temptation to make the null check explicit...
WHERE your_column <> ''
AND your_column IS NOT NULL
...as @Martin Smith demonstrates in the accepted answer, it doesn't really add anything (and I personally shun SQL nulls entirely nowadays, so it wouldn't apply to me anyway!).
sql server - How to use NULL or empty string in SQL - Stack Overflow
sql server - SQL statement to check for empty string - T-SQL - Stack Overflow
What is the best practice for querying if a TEXT column is not empty?
Check the Variable is Empty or Null – SQLServerCentral Forums
I have a table which contains some values which are not null. I thought it might be an empty sting but it was not an empty string either.
I copied that particular field from the table and tried an update statement to convert it into null but it didn't worked either . Kindly suggest something
Select *
From Table
Where (col is null or col = '')
Or
Select *
From Table
Where IsNull(col, '') = ''
If you need it in SELECT section can use like this.
SELECT ct.ID,
ISNULL(NULLIF(ct.LaunchDate, ''), null) [LaunchDate]
FROM [dbo].[CustomerTable] ct
You can replace the null with your substitution value.
This is a simplified query. The original is very long against a very large table:
SELECT * from table where notes <> "";
The `notes` column is a text field and since it has no defined length, it cannot be indexed. When I removed the `notes` <> "" from the complex query, there is a 60% improvement (20 seconds vs 7 seconds). I'm thinking of adding an indexed `has_notes` bit field but now I have to maintain the integrity at the application level. The other is to convert the notes column to a varchar with a large enough size, then create an index.
What is the best practice for querying if a TEXT column is not empty?
I hate empty strings.
That being said, disallowing empty string as a policy doesn't solve the underlying problem; which is "garbage in=garbage out".
If I'm a developer who knows what I'm not allowed to use but I think it's stupid, I'll find an exception.
- no empty string? I'll insert
'x' - no single character? I'll insert
'xx' - some regex filter abomination? random text algorithms are cheap.
Disallow empty string (or other patterns) if you have client issues with garbage data. But whenever you're setting procedural rules to pre filter crap, make sure you reduce the demand as well as the supply. Figure out why people are demanding crap data and give them an alternative instead of fighting them at the tap.
Alternately you could use an additive check constraint or a custom data type to enforce certain input constraints 🤷♂️
If I'm expecting the value of the field to be not null, shouldn't I also always have a constraint that does not allow for zero length strings or space only strings
Yes. Yes you should.
It's ANSI standard behavior to enable storing empty strings, but that doesn't mean it's a good idea. And you especially should not allow both Null and '' in a column.
And note that because strings are compared without trailing spaces,
check (SomeCol<>'')
suffices for both.