Select Non-Empty Columns Using SQL Server

Select non-empty columns using SQL Server

I do not know if this is faster, but you might use one trick: FOR XML AUTO will ommit columns without content:

DECLARE @tbl TABLE(col1 INT,col2 INT,col3 INT);
INSERT INTO @tbl VALUES (1,2,NULL),(1,NULL,NULL),(NULL,NULL,NULL);

SELECT * 
FROM @tbl AS tbl
FOR XML AUTO

This is the result: col3 is missing...

<tbl col1="1" col2="2" />
<tbl col1="1" />
<tbl />

Knowing this, you could find the list of columns, which are not NULL in all rows, like this:

DECLARE @ColList VARCHAR(MAX)=
STUFF
(
    (
    SELECT DISTINCT ',' + Attr.value('local-name(.)','nvarchar(max)')
    FROM
    (
        SELECT
        (
            SELECT *
            FROM @tbl AS tbl
            FOR XML AUTO,TYPE
        ) AS TheXML
    ) AS t
    CROSS APPLY t.TheXML.nodes('/tbl/@*') AS A(Attr) 
    FOR XML PATH('')
    ),1,1,''
);

SELECT @ColList

The content of @ColList is now col1,col2. This string you can place in a dynamically created SELECT.

UPDATE: Hints

It would be very clever, to replace the SELECT * with a column list created from INFORMATION_SCHEMA.COLUMNS excluding all not-nullable. And - if needed and possible - types, wich contain very large data (BLOBs).

UPDATE2: Performance

Don't know what your very large data means actually... Just tried this on a table with about 500.000 rows (with SELECT *) and it returned correctly after less than one minute. Hope, this is fast enough...

SQL Select ONLY Non-Empty Columns

I would write this like the following in order to use indexes if any exists:

Select * 
From TableName 
Where ColumnA Is Not NULL And ColumnA <> '' 
  And ColumnB Is Not NULL And ColumnB <> ''
.......

SQL Server: SELECT 4 non-empty columns and concatenate them

I don't really get why you want to pad these up to 163 characters, but here's an example that shows all possible combinations of empty and non-empty addresses:

declare @MailsTable table
(
    RowNumber int,
    Mail1 varchar(40),
    Mail2 varchar(40),
    Mail3 varchar(40),
    Mail4 varchar(40)
);

insert @MailsTable values
    (0, null, null, null, null),
    (1, 'Addr1', null, null, null),
    (2, null, 'Addr2', null, null),
    (3, 'Addr1', 'Addr2', null, null),
    (4, null, null, 'Addr3', null),
    (5, 'Addr1', null, 'Addr3', null),
    (6, null, 'Addr2', 'Addr3', null),
    (7, 'Addr1', 'Addr2', 'Addr3', null),
    (8, null, null, null, 'Addr4'),
    (9, 'Addr1', null, null, 'Addr4'),
    (10, null, 'Addr2', null, 'Addr4'),
    (11, 'Addr1', 'Addr2', null, 'Addr4'),
    (12, null, null, 'Addr3', 'Addr4'),
    (13, 'Addr1', null, 'Addr3', 'Addr4'),
    (14, null, 'Addr2', 'Addr3', 'Addr4'),
    (15, 'Addr1', 'Addr2', 'Addr3', 'Addr4');

with ConcatenatedAddressesCTE as
(
    select
        RowNumber,
        Mails =
            case when Mail1 != '' then ';' + Mail1 else '' end +
            case when Mail2 != '' then ';' + Mail2 else '' end +
            case when Mail3 != '' then ';' + Mail3 else '' end +
            case when Mail4 != '' then ';' + Mail4 else '' end
    from
        @MailsTable
    where
        Mail1 != '' or Mail2 != '' or Mail3 != '' or Mail4 != ''
)
select
    RowNumber,
    FormattedMails = substring(Mails, 2, 200) + replicate(' ', 164 - len(Mails))
from
    ConcatenatedAddressesCTE;

Results:

RowNumber  FormattedMails
1          Addr1                                                                                                                                                              
2          Addr2                                                                                                                                                              
3          Addr1;Addr2                                                                                                                                                        
4          Addr3                                                                                                                                                              
5          Addr1;Addr3                                                                                                                                                        
6          Addr2;Addr3                                                                                                                                                        
7          Addr1;Addr2;Addr3                                                                                                                                                  
8          Addr4                                                                                                                                                              
9          Addr1;Addr4                                                                                                                                                        
10         Addr2;Addr4                                                                                                                                                        
11         Addr1;Addr2;Addr4                                                                                                                                                  
12         Addr3;Addr4                                                                                                                                                        
13         Addr1;Addr3;Addr4                                                                                                                                                  
14         Addr2;Addr3;Addr4                                                                                                                                                  
15         Addr1;Addr2;Addr3;Addr4                                                                                                                                            

The CTE prefixes every non-empty address with a semicolon so that it doesn't have to worry about how many empty addresses might occur between two non-empty addresses. This means that every result it produces will start with a semicolon, which is why the final part of the query strips off the leftmost character, and why it uses 164 rather than 163 in the length calculation.

I have assumed here that when you say an "empty" mail, you could mean either a null or an empty string.

How to select non empty value for columns from each group?

For the column col3 you need a CASE statement and for the column col4 simply MAX() (or MIN()):

select 
  col1, 
  col2,
  case when sum(case when col3 = 'AB' then 1 else 0 end) > 0 then 'AB' else null end col3,
  max(col4) col4
from test 
group by col1, col2;

See the demo.

Results:

| col1 | col2 | col3 | col4 |
| ---- | ---- | ---- | ---- |
| 12   | 35   |      |      |
| 123  | 345  | AB   | 001  |
| abc  | cde  | AB   |      |
| ooo  | zzz  |      | 001  |

Need help selecting non-empty column values from MySQL

Also look for the columns not equal to the empty string ''

SELECT * FROM `table` WHERE column IS NOT NULL AND column <> ''

If you have fields containing only whitespace which you consider empty, use TRIM() to eliminate the whitespace, and potentially leave the empty string ''

SELECT * FROM `table` WHERE column IS NOT NULL AND TRIM(column) <> ''

Select columns with NULL values only

Here is the sql 2005 or later version: Replace ADDR_Address with your tablename.

declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'ADDR_Address'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM ADDR_Address WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

SQL query to find columns having at least one non null value

I would not recommend using count(distinct) because it incurs overhead for removing duplicate values. You can just use count().

You can construct the query for counts using a query like this:

select count(col1) as col1_cnt, count(col2) as col2_cnt, . . .
from t;

If you have a list of columns you can do this as dynamic SQL. Something like this:

declare @sql nvarchar(max);

select @sql = concat('select ',
                     string_agg(concat('count(', quotename(s.value), ') as cnt_', s.value),
                      ' from t'
                    )
from string_split(@list) s;

exec sp_executesql(@sql);

This might not quite work if your columns have special characters in them, but it illustrates the idea.

Retrieving all non empty tables with specific column name

Use a join between results to fetch final results.

select a11.table_name, a11.row_count, a11.[object_id]
from (SELECT r.table_name, r.row_count, r.[object_id]
FROM sys.tables t
INNER JOIN (
SELECT OBJECT_NAME(s.[object_id]) table_name, SUM(s.row_count) row_count, s. 
[object_id]
FROM sys.dm_db_partition_stats s
WHERE s.index_id in (0,1)
GROUP BY s.[object_id]) 
r on t.[object_id] = r.[object_id]
WHERE r.row_count > 0
) a11
join 
(Select table_name
From INFORMATION_SCHEMA.COLUMNS
Where COLUMN_NAME like '%company%' 
) a12
on a11.table_name = a12.table_name
order by a11.table_name

---

Related Topics