Declare @myVariable varchar(50) = space(50)
, @myData varchar(50) = 'New York City';
Select @myVariable + '|';
Select len(@myVariable), datalength(@myVariable);
Select len(@myData), datalength(@myData);
Select Case When len(@myVariable) = 0 Then 'All Spaces' End;
Select Case When len(@myData) <> 0 Then 'Not All Spaces' End;
This is what @mmkrishna1919 was trying to show you - if the column has all spaces then checking the length of the column will give you a value of 0 for the length. If it does not have all spaces you will get a value greater than 0...
So your check is very simple...
Where len(YourColumn) <> 0
But - you don't need LEN - you can do the following and it will work...and in fact perform much better because you are not using a function on the column which could prevent an index from being used.
Select Case When @myVariable = '' Then 'All Spaces' End;
So now your check to find the 'blank' columns is:
Where YourColumn = ''
To find all non-blanks just use YourColumn > '' and you can eliminate the rows where that column is blank or completely filled with spaces.