SQL - Select multiple columns which are NOT NULL and NOT EMPTY

Question

Table 'anbieter' looks like :

Firma      | Blumenerde | Dachsubstrate | Rinden  | Substrate
-------------------------------------------------------------
Name 1     |             |       x       |         |
Name 2     |      x      |               |    x    |
Name 3     |      x      |       x       |    x    |
Name 4     |             |       x       |         |

What I need to do is:

Output Names (First Column) and select all Columns header which are NOT NULL and not empty ('')

For Example

Name 1 / Dachsubstrate
Name 2 / Blumenerde / Rinde
....

I tried the following:

SELECT * FROM anbieter WHERE Blumenerde IS NOT NULL AND TRIM(Blumenerde) <> ''

This works great with one column... But I need to make it work with some more columns like:

SELECT * FROM anbieter WHERE (Dachsubstrate OR Blumenerde) IS NOT NULL AND TRIM( ????? ) <> '';

How to "TRIM()" them ?

Hope you understand my point...

I just need to output alle column header which are NOT NULL <> ' ' depending on my WHERE() criteria — Sebastian Großmann, Jan 25 '16 at 22:41
Possible duplicate of [Check if a parameter is null or empty in a stored procedure](http://stackoverflow.com/questions/9855331/check-if-a-parameter-is-null-or-empty-in-a-stored-procedure) — Anthony, Jan 25 '16 at 22:56
@SebastianGroßmann If the answer you accepted is right, your example is wrong. Please correct the question to say what you really want. — Barmar, Jan 25 '16 at 23:12

Barmar · Answer 1 · 2016-01-26T00:42:33.010

2

Try this:

SELECT Firma, 
        CONCAT_WS(' / ', 
            IF(TRIM(Blumenerde) != '', 'Blumenerde', NULL),
            IF(TRIM(Dachsubstrate) != '', 'Dachsubstrate', NULL),
            IF(TRIM(Rinden) != '', 'Rinden', NULL),
            IF(TRIM(Substrate) != '', 'Substrate', NULL)) AS cols
FROM anbieter
HAVING cols != ''

CONCAT_WS skips over arguments that are NULL.

DEMO

edited Jan 26 '16 at 00:42

answered Jan 25 '16 at 23:07

Barmar

741,623
53
500
612

This matches the output the OP edited into their post, unlike my answer which provides a completely different result, i have no idea which answer is correct though – Steve Jan 25 '16 at 23:29

score 1 · Answer 2 · answered Jan 25 '16 at 22:56

Is this what you seek?

Select firma, concat(REPLACE(coalesce(Blumenerde,''),'x','Blumenerde'),REPLACE(coalesce(Dachsubstrate,''),'x','Dachsubstrate'),REPLACE(coalesce(Rinden,''),'x','Rinden'),REPLACE(coalesce(Substrate,''),'x','Substrate')
from anbieter

Steve · Accepted Answer · 2016-01-25T23:30:41.570

0

looks like you just need to chain the conditions with AND:

SELECT * FROM anbieter 
WHERE TRIM(Blumenerde) <> ''
AND TRIM(Dachsubstrate) <> ''
AND ...

Also, as mentioned in comments, NOT NULL is a surplus condition

edited Jan 25 '16 at 23:30

answered Jan 25 '16 at 22:50

Steve

20,703
5
41
67

Well ... Got it! Replaced AND with OR SELECT * FROM anbieter WHERE Blumenerde IS NOT NULL AND TRIM(Blumenerde) <> '' OR Dachsubstrate IS NOT NULL AND TRIM(Dachsubstrate) <> '' – Sebastian Großmann Jan 25 '16 at 22:56
Well it should return the 3rd row, where `Firma = Name 3 ` – Steve Jan 25 '16 at 22:56
@SebastianGroßmann Does this really do what you want? It's only returning the rows that have all the columns filled in. It won't return `Name 1: Dachsubstrate` – Barmar Jan 25 '16 at 23:02
2

The `IS NOT NULL` conditions are redundant, since the inequality tests already exclude NULL values. `TRIM(NULL)<>''` will never return TRUE. – spencer7593 Jan 25 '16 at 23:13
1

@SebastianGroßmann Can you explain why you accepted this, since it doesn't produce output like your example? – Barmar Jan 26 '16 at 00:43
@Barmar - i had problems building the query with multiple TRIM (didnt know how to) this pushed me in the right direction to make it work... So it was a part-answered. My main questions was "how to TRIM" for everything else i had a workaround.... but i will Check your answer tomorrow too, maybe it fits better to my criterias but its late in Germany right now. Thank You! – Sebastian Großmann Jan 26 '16 at 01:03

score 0 · Answer 4 · answered Jan 25 '16 at 23:01

You want to check if any of the fields have a value, so in other words if at least one field is not null (which is a bit different from querying for rows where all fields are not null). So you could use (based on your example):

SELECT * FROM anbieter 
WHERE 
NULLIF(Blumenerde, '') IS NOT NULL OR
NULLIF(Dachsubstrate, '') IS NOT NULL OR
NULLIF(Rinden, '') IS NOT NULL OR
NULLIF(Substrate, '') IS NOT NULL

Or you could use COALESCE to check that at least one value is not null:

SELECT * FROM anbieter 
WHERE 
COALESCE(
    NULLIF(Blumenerde, ''),
    NULLIF(Dachsubstrate, ''),
    NULLIF(Rinden, ''),
    NULLIF(Substrate, '')
) IS NOT NULL

SQL - Select multiple columns which are NOT NULL and NOT EMPTY

4 Answers4