CODEWITHSUNDEEP
sqlsql-server
user2919579
user2919579

Reputation: 49

How to check a condition against all the columns of a table?

I have a table which has more than 30 columns(all are varchar). I need to list out all the columns which contains blank i.e.' ' values.

I tried using 'coalesce' but it is only for NULL.

Upvotes: 2

Views: 2602

Answers (6)

Eralper
Eralper

Reputation: 6612

I created a dynamic SQL script that you can use by providing the table name only Here it is

declare @sql nvarchar(max)
declare @table sysname = 'ProductAttributes'

select @sql = 
    'select * from ' + @table + ' where ' +
    string_agg('[' + name + '] = '' '' ', ' and ')
from sys.columns 
where object_id = OBJECT_ID(@table)

select @sql

exec sp_executesql @sql

Unfortunately, for SQL string concatenation String_Agg function is new with SQL Server 2017 But it is also possible to use SQL XML Path to concatenate WHERE clause fragments

SELECT  @sql = 'select * from ' + @table + ' where ' +
  STUFF(
    (
    SELECT
      ' and ' + '[' + [name] + '] = '' '' '
    from sys.columns 
    where object_id = OBJECT_ID(@table)
    FOR XML PATH(''),TYPE
    ).value('.','VARCHAR(MAX)'
    ), 1, 5, ''
  )

select @sql as sqlscript
exec sp_executesql @sql

Upvotes: 0

Ullas
Ullas

Reputation: 11556

If you want like select * from [your_table_name] where [col1] = '' and [col2] = ''....., then use dynamic sql query like below.

Query

declare @sql as varchar(max);
select @sql = 'select * from [your_table_name] where ' 
    + stuff((
            select ' and [' + [column_name] + '] = ' + char(39) + char(39)
            from information_schema.columns
            where table_name = 'your_table_name'
            for xml path('')
    ) 
    , 1, 5, ''
);

exec(@sql);

Update

Or else if you want to list the column names which have a blank value, then you can use the below dynamic sql query.

Query

declare @sql as varchar(max);
select @sql = stuff((
        select ' union all select ' + [column_name] + ' as [col1], ' 
        + char(39) + [column_name] + char(39) + ' as [col2]'
        + ' from your_table_name'
        from information_schema.columns
        where table_name = 'your_table_name'
        for xml path('')
    )
    , 1, 11, ''
); 

set @sql = 'select distinct t.col2 as [blank_cols] from(' + @sql 
+ ')t 
where coalesce(ltrim(rtrim(t.col1)), ' + char(39) + char(39) + ') = ' 
+ char(39) + char(39) + ';';

exec(@sql);

Find a demo here

But still I'm not sure that this is what you are looking out for.

Upvotes: 2

Rigerta
Rigerta

Reputation: 4039

The following query will give you all the columns in a table that might have null or '' values. It is written so that you can run it for all tables in your database but you can limit it to a single table, as I have done for this specific example, checking a table called testingNulls:

--two variables needed for table name and column name, when looping through all tables
declare @table varchar(255), @col varchar(255), @sql varchar(max)

--this will be used to store the result, to have one result set instead of one row per each cursor cycle
if object_id('tempdb..#nullcolumns') is not null drop table #nullcolumns
create table #nullcolumns (tablename varchar(255), columnname varchar(255))

declare getinfo cursor for
select t.name tablename, c.name 
from sys.tables t join sys.columns c on t.object_id = c.object_id 
where t.name = 'testingnulls' --here the condition for the table name
open getinfo
fetch next from getinfo into @table, @col
while @@fetch_status = 0
begin
  select @sql = 'if exists (select top 1 * from [' + @table + '] where [' + @col + '] is null or [' + @col + '] like '''' ) begin insert into #nullcolumns select ''' + @table + ''' as tablename, ''' + @col + ''' as all_nulls end'
  print(@sql)
  exec(@sql)
  fetch next from getinfo into @table, @col
end
close getinfo
deallocate getinfo

--this should be the result you need:
select * from #nullcolumns

You can see a working example here. I hope this is what you need.

Upvotes: 4

Radim Bača
Radim Bača

Reputation: 10701

If you want to count number of columns having '' value in a table (not for each row) then use the following

SELECT max(CASE WHEN col1 = '' THEN 1 ELSE 0 END) +
       max(CASE WHEN col2 = '' THEN 1 ELSE 0 END) +
       max(CASE WHEN col3 = '' THEN 1 ELSE 0 END) +
       ...
FROM t

demo

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

List all columns that contain a blank in some record? You'd use a query per column and collect the results with UNION ALL:

select 'COL1' where exists (select * from mytable where col1 like '% %')
union all
select 'COL2' where exists (select * from mytable where col2 like '% %')
union all
...
union all
select 'COL30' where exists (select * from mytable where col30 like '% %');

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

you have not many choices but to specify all the columns in your where clause

WHERE COL1 = '' AND COL2 = '' AND COL3 = '' AND . . .

or you can use Dynamic SQL to form your query, but that is not an easy path to go

Upvotes: 1

Related Questions