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!
Answer from Larry Coleman on Stack ExchangeVideos
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'd rather not write a bunch of AND clauses, so is there a quick, efficient way to do this?
I'm importing some data with 10 fields into a SQL Server from a CSV file. Occasionally this file has null/empty values across all the cells/columns.
What I'd like to do is just write one relatively short sql statement to simply count (at first) all these rows. I'd rather do it without doing something like:
...and (column1 is null or column1 = '')
...and (column2 is null or column2 = '')
etc...
Is there a good way to do this, or am I stuck with the above?
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
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!).
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?
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.
You can use
SELECT * FROM table1
WHERE NOT (Column1 IS NULL OR
Column2 IS NULL OR
Column3 IS NULL OR
Column4 IS NULL
IS NOT NULL)
As per OP comment, Updating answer
Inserting Rows by Using INSERT and SELECT Subqueries
INSERT INTO Table_A
SELECT column1, column2, column3,column4
FROM Table_B
WHERE NOT (Column1 IS NULL OR
Column2 IS NULL OR
Column3 IS NULL OR
Column4 IS NULL
IS NOT NULL);
Your query
I am able to reduce 50 chars approx
SELECT * FROM AB_DS_TRANSACTIONS
WHERE
FK_VIOLATION IS NULL
AND TRANSACTION_ID NOT
IN(SELECT distinct TRANSACTION_ID FROM AB_TRANSACTIONS)
AND
NOT (
COUNTRY_ID IS NULL
OR GEO_CUST_COUNTRY_ID IS NULL
OR INVOICE_DATE IS NULL
OR ABB_GLOBALID IS NULL
OR SALES_ORG_ID IS NULL
OR DIST_ID IS NULL
OR CUSTOMER_ID IS NULL
OR REPORT_UNIT_ID IS NULL
OR CURR_INVOICE IS NULL
OR DIVISION_CODE IS NULL
)
SELECT * FROM AB_DS_TRANSACTIONS
WHERE COALESCE(COUNTRY_ID,GEO_CUST_COUNTRY_ID,INVOICE_DATE,ABB_GLOBALID,SALES_ORG_ID,DIST_ID,CUSTOMER_ID,REPORT_UNIT_ID,CURR_INVOICE,DIVISION_CODE) IS NOT NULL
Try this:
SELECT Firma,
CONCAT_WS(' / ',
IF(TRIM(Blumenerde) != '', 'Blumenerde', NULL),
IF(TRIM(Dachsubstrate) != '', 'Dachsubstrate', NULL),
IF(TRIM(Rinden) != '', 'Rinden', NULL),
IF(TRIM(Substrate) != '', 'Substrate', NULL)) AS cols
FROM anbieter
HAVING cols != ''
CONCAT_WS skips over arguments that are NULL.
DEMO
Is this what you seek?
Select firma, concat(REPLACE(coalesce(Blumenerde,''),'x','Blumenerde'),REPLACE(coalesce(Dachsubstrate,''),'x','Dachsubstrate'),REPLACE(coalesce(Rinden,''),'x','Rinden'),REPLACE(coalesce(Substrate,''),'x','Substrate')
from anbieter
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.
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
An extension to @db2's answer with less (read:zero) hand-wrangling:
DECLARE @tb nvarchar(512) = N'dbo.[table]';
DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
+ N' WHERE 1 = 0';
SELECT @sql += N' OR ' + QUOTENAME(name) + N' IS NULL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tb)
AND is_nullable = 1;
EXEC sys.sp_executesql @sql;
You should list out all the columns as per JNK's comment.
WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL
A somewhat less efficient approach that avoids this is below though.
;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS ns)
SELECT *
FROM YourTable AS T1
WHERE (
SELECT T1.*
FOR XML PATH('row'), ELEMENTS XSINIL, TYPE
).exist('//*/@ns:nil') = 1
(Based on this SO answer)