MySQL CONCAT_WS from many variables - how can I limit the query to merely existing data?

Question

I have been looking through all other mentionings of "MySQL" and "CONCAT_WS" but they do not address my problem. I have a medical database set up in MySQL (5.5.6x) with 180 tables and several thousand fields. The handling of database tables and forms is done through a large PHP application.

Part of my SQL query is the following code:

    CONCAT_WS(""
        , COALESCE(CASE WHEN op.OP5Begleiteingriff1 = "2" THEN "Cholezystektomie  " ELSE CONCAT("", "") END, "NULL")
        , COALESCE(CASE WHEN op.OP5Begleiteingriff2 = "2" THEN "Appendektomie  " ELSE CONCAT("", "") END, "NULL")
        , COALESCE(CASE WHEN op.OP5Begleiteingriff3 = "2" THEN "Adhäsiolyse  " ELSE CONCAT("", "") END, "NULL")
        , COALESCE(CASE WHEN op.OP5Begleiteingriff4 = "2" THEN "Antrum-Resektion  " ELSE CONCAT("", "") END, "NULL")
        , COALESCE(CASE WHEN op.OP5Begleiteingriff5 = "2" THEN "Hiatoplastie  " ELSE CONCAT("", "") END, "NULL")
        , COALESCE(CASE WHEN op.OP5Begleiteingriff6 = "2" THEN "Hernien-Reparatur  " ELSE CONCAT("", "") END, "NULL")
        , COALESCE(CASE WHEN op.OP5Begleiteingriff7 = "2" THEN "Band-Entfernung  " ELSE CONCAT("", "") END, "NULL")
        , COALESCE(CASE WHEN op.OP5Begleiteingriff8 = "2" THEN "Fundus-Resektion  " ELSE CONCAT("", "") END, "NULL")
        , COALESCE(CASE WHEN op.OP5Begleiteingriff9 = "2" THEN "Rest-Gastrektomie  " ELSE CONCAT("", "") END, "NULL")
        , COALESCE(CASE WHEN op.OP5Begleiteingriff10 = "2" THEN "Leber-Biopsie  " ELSE CONCAT("", "") END, "NULL")
        , COALESCE(CASE WHEN op.OP5Begleiteingriff99 = "2" THEN "Andere Begleiteingriffe" ELSE CONCAT("", "") END, "NULL")
        ) AS "Begleiteingriffe (OP 5)",

This denotes the content extraction of the fields

    op.OP5Begleiteingriffx = "2"

(= "2" means that the respective checkbox has been clicked) from x equalling -1 to -10 or -99.

Now, I want to limit the display of results to those fields which are not empty.

Is there an elegant way to do this in a short fashion?

So far, I have implemented something like this:

    FROM dat_patient p
    LEFT OUTER JOIN dat_optherapie op ON op.patID = p.ID
    LEFT OUTER JOIN users_benutzer ub ON ub.ID = p.UserID

    WHERE op.OP1Datum BETWEEN "1950-01-01" AND "2050-12-31"
    AND (
        "Begleiteingriffe (OP 1)" != "" OR "Begleiteingriffe (OP 2)" != "" OR "Begleiteingriffe (OP 3)" != "" OR "Begleiteingriffe (OP 4)" != "" OR "Begleiteingriffe (OP 5)" != ""
        )
    ORDER BY p.Nachname, p.Vorname, p.Gebdatum; ';

It still delivers ALL results and does not omit the empty fields:

Is it possible at all to handle it this way or do I need to set up a handling for every

    op.OP5Begleiteingriffx

like in the above-mentioned AND ... statement?

Answer 1

OWN SOLUTION:

It does not work regarding the use of

    "Begleiteingriffe (OP 1)" != ""

or

    "Begleiteingriffe (OP 1)" IS NOT NULL

but works when every single possibility is handled as such in

    WHERE (
        op.OP1Begleiteingriff1 = "2" OR
        op.OP1Begleiteingriff2 = "2" OR
        op.OP1Begleiteingriff3 = "2" OR
        op.OP1Begleiteingriff4 = "2" OR
        op.OP1Begleiteingriff5 = "2" OR
        op.OP1Begleiteingriff6 = "2" OR
        op.OP1Begleiteingriff7 = "2" OR
        op.OP1Begleiteingriff8 = "2" OR
        op.OP1Begleiteingriff9 = "2" OR
        op.OP1Begleiteingriff10 = "2" OR
        op.OP1Begleiteingriff99 = "2"
    )

Now, I only get displayed results where there are no more empty fields: