Select *
From Table
Where (col is null or col = '')
Or
Select *
From Table
Where IsNull(col, '') = ''
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.
feature comparison - When to use NULL and when to use an empty string? - Database Administrators Stack Exchange
design - SQL: empty string vs NULL value - Software Engineering Stack Exchange
Check the Variable is Empty or Null – SQLServerCentral Forums
Sql Connector - Write NULL intead of empty in SQL Database
Videos
Lets say i have a Excel sheet , col : name and ID . some rows cells in "name" are empty in datasheet.
now sql pulls this data, will the empty cells be filled with a null value by default?
or is null a unique type of character that is only used to overwrite existing values , so when system reads it it thinks its empty?
Let's say that the record comes from a form to gather name and address information. Line 2 of the address will typically be blank if the user doesn't live in apartment. An empty string in this case is perfectly valid. I tend to prefer to use NULL to mean that the value is unknown or not given.
I don't believe the physical storage difference is worth worrying about in practice. As database administrators, we have much bigger fish to fry!
I do not know about MySQL and PostgreSQL, but let me treat this a bit generally.
There is one DBMS namely Oracle which doesn't allow to choose it's users between NULL and ''. This clearly demonstrates that it is not necessary to distinguish between both. There are some annoying consequences:
You set a varchar2 to an empty string like this:
Update mytable set varchar_col = '';
the following leads to the same result
Update mytable set varchar_col = NULL;
But to select the columns where the value is empty or NULL, you have to use
select * from mytable where varchar_col is NULL;
Using
select * from mytable where varchar_col = '';
is syntactically correct, but it never returns a row.
On the other side, when concatenating strings in Oracle. NULL varchars are treated as empty strings.
select NULL || 'abc' from DUAL;
yields abc. Other DBMS would return NULL in these cases.
When you want to express explicitly, that a value is assigned, you have to use something like ' '.
And you have to worry whether trimming not empty results in NULL
select case when ltrim(' ') is null then 'null' else 'not null' end from dual
It does.
Now looking at DBMS where '' is not identical to NULL (e.g. SQL-Server)
Working with '' is generally easier and in most case there is no practical need to distinguish between both. One of the exceptions I know, is when your column represents some setting and you have not empty defaults for them. When you can distinguish between '' and NULL you are able to express that your setting is empty and avoid that the default applies.
I would say that NULL is the correct choice for "no email address". There are many "invalid" email addresses, and "" (empty string) is just one. For example "foo" is not a valid email address, "a@b@c" is not valid and so on. So just because "" is not a valid email address is no reason to use it as the "no email address" value.
I think you're right in saying that "" is not the correct way to say "I don't have a value for this column". "" is a value.
An example of where "" might be a valid value, separate to NULL could be a person's middle name. Not every one has a middle name, so you need to differentiate between "no middle name" ("" - empty string) and "I don't know if this person has a middle name or not" (NULL). There's probably many other examples where an empty string is still a valid value for a column.
While agreeing with the above comments, I would add this argument as a primary motivation:
- It is obvious to any programmer looking at a database that a field marked NULL is an Optional field. (i.e. the record doesn't require data for that column)
- If you mark a field NOT NULL, any programmer should intuitively assume that it is a Required field.
- In a field that allows nulls, programmers should expect to see nulls rather than empty strings.
For the sake of Self-Documenting Intuitive Coding, use NULL instead of empty strings.