Check for blank in SQL

Transact-SQL
1

Hello All,

May anyone please help me on this.

/*
Whenever value column is empty or null then only it should appear in the output.
for eg: Desc 1 Value is blank or null or desc 2 Value is blank or null or 3 is null or blank then it should appear in output
if all value is filled then it should not appear in output.

*/
Declare @table table
(
Brand varchar (50),
Desc1 varchar (50),
Value1 varchar (50),
Desc2 varchar (50),
Value2 varchar (50),
Desc3 varchar (50),
Value3 varchar (50),
Desc4 varchar (50),
Value4 varchar (50)
)

Insert @table

select 'Cooler', 'Type', 'Air To Air', 'Number Of Pieces','' ,'Design', '', 'Height','Disc' UNION ALL
select 'AmericanAuto', 'Type', 'Universal' , 'Number Of Pieces', '4' ,'Design', 'NoLogo', 'Height', '' UNION ALL
select 'AmericanAuto', 'Type', 'Universal', 'Number Of Pieces', '4' ,'Design', '' , 'Height', '' UNION ALL
select 'FloorMat', 'Type', ' ' , 'Number Of Pieces', '6' ,'Design', 'Universal','Height', 'Black' UNION ALL
select 'LINK', 'Type', 'Black' , 'Number Of Pieces', '8' ,'Design', 'Rubber', 'Height', 'RED' UNION ALL
select 'LINK', 'Type', 'Gray' , 'Number Of Pieces', '8' ,'Design', 'Rubber', 'Height', 'RED' UNION ALL
select 'LINK', 'Type', 'Black', 'Number Of Pieces', '8', 'Design', 'Rubber', 'Height', 'RED'

SELECT * FROM @table
/*

AND OUTPUT Should be

all those records which contain atleast one blank or NULL in a attribute.

'Cooler', 'Type', 'Air To Air', 'Number Of Pieces','' ,'Design', '', 'Height','Disc'
'AmericanAuto', 'Type', 'Universal' , 'Number Of Pieces', '4' ,'Design', 'NoLogo', 'Height', ''
'AmericanAuto', 'Type', 'Universal', 'Number Of Pieces', '4' ,'Design', '' , 'Height', ''
'FloorMat', 'Type', ' ' , 'Number Of Pieces', '6' ,'Design', 'Universal','Height', 'Black'

Brand Link records will not appear in output as it doesn't contain any null or blank in any of the attribute
*/
Thanks a lot

2

SELECT *
FROM @table
where
isnull(Desc1,'') = ''
OR isnull(Value1,'') = ''
OR isnull(Desc2,'') = ''
OR isnull(Value2,'') = ''
OR isnull(Desc3,'') = ''
OR isnull(Value3,'') = ''
OR isnull(Desc4,'') = ''
OR isnull(Value4,'') = ''

3 
select * from @table
where isnull(Brand, '') = ''
   or isnull(Desc1, '') = ''
   or isnull(Value1, '') = ''
   or isnull(Desc2, '') = ''
   or isnull(Value2, '') = ''
   or isnull(Desc3, '') = ''
   or isnull(Value3, '') = ''
   or isnull(Desc4, '') = ''
   or isnull(Value4, '') = ''
4

Thank you Techie,

is it possible to show if value1 is null then desc1.
so output consists of brand , desc 1, desc2 , desc3..... only.
i don't want to display value column.

Thanks again

5

like this:

select case when ISNULL(Brand , '' ) = '' then 'Brand' else Brand end AS Brand
     , case when ISNULL(Desc1 , '' ) = '' then 'Desc1' else Desc1 end AS Desc1
     , case when ISNULL(Value1, '' ) = '' then 'Value1' else Value1 end AS Value1
     , case when ISNULL(Desc2 , '' ) = '' then 'Desc2' else Desc2 end AS Desc2
     , case when ISNULL(Value2, '' ) = '' then 'Value2' else Value2 end AS Value2
     , case when ISNULL(Desc3 , '' ) = '' then 'Desc3' else Desc3 end AS Desc3
     , case when ISNULL(Value3, '' ) = '' then 'Value3' else Value3 end AS Value3
     , case when ISNULL(Desc4 , '' ) = '' then 'Desc4' else Desc4 end AS Desc4 
     , case when ISNULL(Value4, '' ) = '' then 'Value4' else Value4 end AS Value4
 from @table
where isnull(Brand, '') = ''
   or isnull(Desc1, '') = ''
   or isnull(Value1, '') = ''
   or isnull(Desc2, '') = ''
   or isnull(Value2, '') = ''
   or isnull(Desc3, '') = ''
   or isnull(Value3, '') = ''
   or isnull(Desc4, '') = ''
   or isnull(Value4, '') = ''
6

/*
'Cooler', '', 'Number Of Pieces', ,'Design', ''
'AmericanAuto', '' , '' ,'', 'Height'
'AmericanAuto', '', '' ,'Design', 'Height'
'FloorMat', 'Type' , '' ,'','' UNION ALL

Hello gbritton,

i am Sorry if my example was confusing,
i am trying to get Brand Desc1, desc2 and desc 3 in the output and no value column.
and only those desc column will appear whose value column is either null or blank.

Thank You Very Much For your valuable help.
*/

7

Yes, your requirements are confusing! In fact, I still don't understand them. However, I feel confident that you can take the example code that has been posted and use it to produce the actual results you want.

When you're done, be sure to post your solution here, so that others may benefit as well.

8

No, you can't do that, because which column(s) are blank will be different for each row. You can't just leave columns off, you'd have to just show an empty column.

Btw, never use ISNULL() in a WHERE clause (a function makes it "nonsargable"). There are multiple possible corrective rewrites, including:

(Desc1 is null or desc1 = '')
OR (value1 is null or value1 = '')
OR ...

9

@ScottPletcher yes, good point about the SARGAbility of ISNULL(). However I have a feeling that this is not for a production query (could be wrong though). For ad hoc it's good enough

10

Goodness no. In most cases, I have to disagree with that. Unless someone is going to be a piano comedian, they don't intentionally hit the wrong keys when they're playing the piano. Practice doing it right so that you get better at it.

11

Since I play piano a little, I can say that hitting the wrong notes can lead to moments of inspiration (especially with jazz improv).

12

Heh... that would be absolutely true. Just not normally for neophytes of SQL.

13

A lot depends on how you define "empty or blank space" and which database engine you are using. Assuming that your database engine does standard SQL compares (right side string promotion), I'd suggest something like:

SELECT *
   FROM myTable
   WHERE  myColumn IS  NULL OR '' = myColumn OR myColumn = ' '

Thanks for initiating this topic.
Big data training in chennai

14

Thanks Everyone.

15

Great post! Thanks for sharing us!