Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to remove blank rows from SQL result set

I have a query like this:

SELECT DISTINCT 
    [F_Exhibitor_Name] 
FROM 
    [V_ExhibitorLocation] 
WHERE 
    F_ExhibitionCode ='10996' 
  AND 
    [F_Exhibitor_Name] IS NOT NULL
ORDER BY 
    F_Exhibitor_Name

My first line is blank, which causes an error in the code. My current result set looks like this:

enter image description here

like image 216
jase mhi Avatar asked Mar 16 '23 00:03

jase mhi


2 Answers

In SQL Server, a null and an empty string ('') are not the same. If you which to exclude both, you should explicitly check for both:

SELECT   DISTINCT [F_Exhibitor_Name]
FROM     [V_ExhibitorLocation] 
WHERE    [F_ExhibitionCode] = '10996' AND
         [F_Exhibitor_Name] IS NOT NULL AND
         [F_Exhibitor_Name] <> ''
ORDER BY [F_Exhibitor_Name]
like image 105
Mureinik Avatar answered Mar 18 '23 16:03

Mureinik


I can suggest a trick for mixing IS NOT NULL AND <> '' like this:

SELECT   DISTINCT 
    F_Exhibitor_Name
FROM     
    V_ExhibitorLocation
WHERE    
    F_ExhibitionCode = '10996' 
  AND
    F_Exhibitor_Name > ''   --or ISNULL(F_Exhibitor_Name, '') <> ''
ORDER BY 
    F_Exhibitor_Name
like image 44
shA.t Avatar answered Mar 18 '23 15:03

shA.t