what different between blank and null ?

Question

I work on SQL server 2012 i face issue I have table have column family Name

this column may be Null or blank

so I need to get data from Table Employee when Column Family Name is not null

or not blank so How to do that please ?

select * from Employee where Family Name is not null or ??????????
How to represent is not blank

Answer 1

Hi @ahmed salah ,

I will provide one example from below and you could check whether it is helpful to you.

I would like to use ISNULL to include the blank and null conditions.

declare @Employee  table  
(id int,  
FamilyName varchar(20))  
  
insert into @Employee values  
(1,''),  
(2,null),  
(3,'Mary')  
  
select * from @Employee where FamilyName is null  
--id FamilyName  
--2 NULL  
  
select * from @Employee where FamilyName=''  
--id FamilyName  
--1  
  
select * from @Employee where FamilyName='' or FamilyName is null  
--id FamilyName  
--1  
--2 NULL  
  
select * from @Employee where isnull(FamilyName,'')=''  
--id FamilyName  
--1  
--2 NULL  

In your situation, you could have a try with below queries:

--option 1  
   select * from @Employee where isnull(FamilyName,'')<>''  
--option 2  
    select * from @Employee where FamilyName<>'' and FamilyName is not null  

If there is no index on FamilyName column, you could choose any of them.

If you have index on FamilyName column, you could try with option 2 because of performance benefits since isnull is one function and causes that predicate to be non-SARGable.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

Simple , BLANK is a known value but NULL us unknown value

Answer 3

Consider this solution too:

select * 
from Employee 
where [Family Name] <> ''

It will exclude null and blank values.