How to remove empty rows from a SQL result set

Question

How to remove empty rows from a SQL result set

I have a request 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 is causing an error in the code. My current result set looks like this:

enter image description here

+3

null sql sql-server select

jase mhi May 17 '15 at 7:39

source to share

2 answers

I can suggest a trick for mixing IS NOT NULL

ANDs <> ''

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

+1

shA.t May 17 '15 @ 9:05 am

source to share

Mureinik · Accepted Answer · 2015-05-17T07:43:34+0000

In SQL Server null

and empty string ( ''

) do not match. If you want to exclude both, you must explicitly check 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]

How to remove empty rows from a SQL result set

More articles: