CopySelect *
From Table
Where (col is null or col = '')
Or
CopySelect *
From Table
Where IsNull(col, '') = ''
Answer from codingbadger on Stack OverflowCopySelect *
From Table
Where (col is null or col = '')
Or
CopySelect *
From Table
Where IsNull(col, '') = ''
If you need it in SELECT section can use like this.
CopySELECT ct.ID,
ISNULL(NULLIF(ct.LaunchDate, ''), null) [LaunchDate]
FROM [dbo].[CustomerTable] ct
You can replace the null with your substitution value.
What is a null in sql? is it the same as a empty cell?
design - SQL: empty string vs NULL value - Software Engineering Stack Exchange
Query will not return blank results only a null or 0 value
How do I check if a SQL Server text column is empty? - Stack Overflow
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?
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.
You do realize you are returning multiple data types in your query? This is not very good query design if the result is a query table.
The data types for 1 and 0 is integer (whether it is int, smallint, or tinyint) while data type for ‘’ is character.
You also need to define what ‘Blank’ is since this is not a proper value definition in MS SQL. I am going to assume that you want ‘Blank’ to be the same as ‘’ which is a zero-length string of type character (whether it is char, varchar, nchar, or nvarchar).
Another common problem is the use of ‘= NULL’ instead of the proper form ‘IS NULL’ since NULL itself is not a value; in simple terms NULL is the absence of a value.
You should read another post here by Larry Shanahan and his included linked post from Robert Sheldon:
Returning a NULL Value when query returns nothing
Post by Robert Sheldon re NULLs
Back to your SQL code - I believe a better simpler version would be one that avoids the use of NULL altogether and would be the following; this also keeps all returned types as character; very close to your 2nd query version:
CASE
WHEN PAE.SEX = 'F' THEN '1'
WHEN PAE.SEX = 'M' THEN '0'
ELSE ''
END AS SEX,
@larryshanahan
I need to return blank results when there is not a value entered into the table but have only been able to get either a null or 0 value.
CASE
WHEN PAE.SEX = ‘F’ THEN 1
WHEN PAE.SEX = ‘M’ THEN 0
WHEN PAE.SEX = NULL THEN ‘’
END AS SEX,
this returns a null value
CASE
WHEN PAE.SEX = ‘F’ THEN 1
WHEN PAE.SEX = ‘M’ THEN 0
ELSE ‘’
END AS SEX,
this returns a 0 value