0

there are many discussions about string-based results being saved in a comma-separated fashion within a MySQL database table. I do not want to extend this here with my own philosophical comments, I just want to start by saying that this issue is well known to me, but not right the topic now.

The topic is that I have such a situation to evaluate. The data are saved as string-based cyphers. Each of these cyphers denotes a medical complication after a specific type of surgery has been performed.

Example:

MySQL database table "complications" contains a field called "indication for surgical revision" (VARCHAR[50]) in which data entries such as "3, 7, 9, 16" are saved, because from a multiple selection menu these 4 different indications were chosen by the user.

Now I want to do the following:

I know what "3", "7", "9" and "16" stand for. I want to redisplay these cyphers as their original notions (such as "weight regain", "weight loss failure" etc.) using a MySQL SELECT query such as:

SELECT blah blah blah (a lot of other stuff),

CONCAT_WS(", "
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%1%" THEN "Innere Hernie (Meso)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%2%" THEN "Innere Hernie (PETERSEN)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%3%" THEN "Weight Regain  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%4%" THEN "Weight Loss Failure  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%5%" THEN "Anastomosenstenose  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%6%" THEN "Dysphagie  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%7%" THEN "Reflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%8%" THEN "Gallenreflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%9%" THEN "Malnutrition  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%10%" THEN "Diarrhö  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%11%" THEN "Gastrogastrale Fistel" ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%12%" THEN "Ulcusperforation  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%13%" THEN "Chronische Ulcus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%14%" THEN "Chronische Schmerzen  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%15%" THEN "Ileus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%16%" THEN "Choledocholithiasis nach Magen-Bypass  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%17%" THEN "Leakage  " ELSE CONCAT("", "") END, "NULL")
                 ) AS "Indikation zur Revision",

You may ask now "why is this guy so circumstantial?" or "why the heck doesn't he do it using PHP?". I do it using PHP, but in a different context. Here, I need to have a direct evaluation using a SQL query because in this code which is not by me all data is extracted to automatically create result sets within Excel CSV files to be secondarily processed - and I do not want to reinvent the wheel anew.

Now, the above-mentioned query does not do what I want it to do. In my example, I would like to get a display of "Weight Regain, Reflux, Malnutrition, Choledocholithiasis nach Magen-Bypass" out of this "3, 7, 9, 16" string.

I am aware of the fact that ... LIKE "%3%" ... does not do the trick here.

There are some recommendations of using "FIND_IN_SET('3', op.OP2RevisionIndikation)", but here we are not directly in the main SELECT statement, but in a CASE WHEN THEN ELSE END procedure with COALESCE and CONCAT_WS.

Does anyone of you have an idea as to how to evaluate that string "3, 7, 9, 16" correctly by mapping it to the original values and have MySQL display it that way?

I hope I have been thorough enough and adequately understandable.

Best regards and thank you infinitely for your help

Markus

mtjmohr
  • 99
  • 9
  • Please allow this question to serve as an example of a pitfall. Why we do not store comma separated lists is discussed in Chapter 2 Jaywalking in Bill Karwin's book "SQL Antipatterns", available at Amazon and other fine booksellers. https://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557 – spencer7593 May 05 '18 at 05:12
  • So you know all the strings to the numbers, you don't have to query them from another table? Maybe a simple (yet painful (matches the hospital environment) deeply nested) string replacement is an option: `substring( ... replace(replace(concat(op.OP2RevisionIndikation, ','), '1,', 'Innere Hernie (Meso),'), '2,', 'Innere Hernie (PETERSEN)') ..., -1, 1)` (I'm not gonna do it for all 17 here, but I guess you get the point.) – sticky bit May 05 '18 at 05:22
  • 2
    I don't see what difference where in the query you are makes, you should still be able to use `FIND_IN_SET`. [SQLFiddle](http://www.sqlfiddle.com/#!9/d7df4e/7) – Nick May 05 '18 at 05:31
  • I think what I'd ask, is why does this guy acknowledge, and then refuse to attend to, the fundamental flaw in his premise? – Strawberry May 05 '18 at 05:37
  • @spencer7593: See my initial remarks, please. I am aware of it. – mtjmohr May 05 '18 at 06:43
  • @sticky bit: Seems to be a good option, I will test it. – mtjmohr May 05 '18 at 06:44
  • @Nick: You are right, but please confer my answer below. – mtjmohr May 05 '18 at 06:44
  • @Strawberry: The original coder has left and I have taken the commission to handle this code. – mtjmohr May 05 '18 at 06:44

3 Answers3

1

In a more general case, when the goal is to "translate" a comma separated list of positive integer values into a list of corresponding strings, in the same order as the original list of values, and without introducing extraneous "commata" and spaces...

We can wield a horrendous expression to achieve that.

As a demonstration.

SELECT op.OP2RevisionIndikation
      , CONCAT_WS(', '
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  1 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  2 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  3 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  4 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  5 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  6 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  7 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  8 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  9 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 10 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 11 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 12 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 13 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 14 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 15 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 16 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 17 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 18 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 19 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 20 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
   ) AS translated
  FROM ( SELECT '' AS OP2RevisionIndikation 
         UNION ALL SELECT '17'         
         UNION ALL SELECT '3'
         UNION ALL SELECT '3, 17'
         UNION ALL SELECT '17, 3, 7'
         UNION ALL SELECT '17 , ,, ,3, flurb, 747, 17'
         UNION ALL SELECT ', x,,,,000017,, 3x  ,,x, 01,,17'
       ) AS op

returns 

OP2RevisionIndikation            translated
-------------------------------  -------------------------------------------------------

17                               Leakage
3                                Weight Regain
3, 17                            Weight Regain, Leakage
17, 3, 7                         Leakage, Weight Regain, Reflux
17 , ,, ,3, flurb, 747, 17       Leakage, Weight Regain, Leakage
^^       ^              ^^
, x,,,,000017,, 3x  ,,x, 01,,17  Leakage, Weight Regain, Innere Hernie (Meso), Leakage
           ^^   ^         ^  ^^

Notes and caveats:

Each of the ELT lines is an exact replica except for one integer that basically specifies which nth element of the comma separated list of integers to extract, the 1st, the 4th, the 5th, etc.

The expression processes only a finite number of elements in the list; a separate ELT expression is required for each element. The example handles up to 20 elements in the comma separated list; this could be extended to handle more elements.

This "works" only for comma separated lists of positive integer values. Each value between commas will be evaluated as an integer. That means an element 'x17' will evaluate to 0. Element '-6.2' will evaluate to -6. Element
'007jamesbond' will evaluate to 7. And so on.

The integer value from the evaulation is used as an index to "lookup" a string from a list of strings in the ELT function.

Elements that evaluate to an integer values that don't correspond to a string in the list (e.g. 0, -6) are ignored, as if the element had not been present in the original list.

The last two row rows in returned in the example demonstrate the behavior with comma separated lists we would probably consider to be malformed. The translation expression is a "best effort", it returns what can be translated. The expression doesn't throw an error, or return NULL or an empty string when the comma separated list is malformed.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thank you for this excessive work. I have understood what you mean and will give it a try tomorrow. – mtjmohr May 05 '18 at 22:35
  • @mtjmohr: This example may not fit your specific use case, I offer no opinion on that. I offer this example as a general example, for the general problem. This differs (significantly) from the original posed in the question. This expression preserves the *order* of elements that are translated, including duplicated elements. – spencer7593 May 06 '18 at 02:14
0

Thanks for your answers so far.

The FIND_IN_SET solution ...

CONCAT_WS(", "
                 , COALESCE(CASE WHEN FIND_IN_SET("1", op.OP2RevisionIndikation) THEN "Innere Hernie (Meso)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("2", op.OP2RevisionIndikation) THEN "Innere Hernie (PETERSEN)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("3", op.OP2RevisionIndikation) THEN "Weight Regain  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("4", op.OP2RevisionIndikation) THEN "Weight Loss Failure  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("5", op.OP2RevisionIndikation) THEN "Anastomosenstenose  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("6", op.OP2RevisionIndikation) THEN "Dysphagie  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("7", op.OP2RevisionIndikation) THEN "Reflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("8", op.OP2RevisionIndikation) THEN "Gallenreflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("9", op.OP2RevisionIndikation) THEN "Malnutrition  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("10", op.OP2RevisionIndikation) THEN "Diarrhö  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("11", op.OP2RevisionIndikation) THEN "Gastrogastrale Fistel" ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("12", op.OP2RevisionIndikation) THEN "Ulcusperforation  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("13", op.OP2RevisionIndikation) THEN "Chronische Ulcus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("14", op.OP2RevisionIndikation) THEN "Chronische Schmerzen  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("15", op.OP2RevisionIndikation) THEN "Ileus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("16", op.OP2RevisionIndikation) THEN "Choledocholithiasis nach Magen-Bypass  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("17", op.OP2RevisionIndikation) THEN "Leakage  " ELSE CONCAT("", "") END, "NULL")
                 ) AS "Indikation zur Revision",

... is able to identify the single items. But it does not handle the comma:

From "3, 7, 9, 16" the "3" is identified correctly, but the rest not at all, so I get only the very first of these items displayed:

enter image description here

mtjmohr
  • 99
  • 9
0

Ahhh, Nick's last comment does the trick:

CONCAT_WS(", "
                 , COALESCE(CASE WHEN FIND_IN_SET("1", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Innere Hernie (Meso)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("2", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Innere Hernie (PETERSEN)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("3", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Weight Regain  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("4", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Weight Loss Failure  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("5", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Anastomosenstenose  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("6", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Dysphagie  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("7", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Reflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("8", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Gallenreflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("9", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Malnutrition  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("10", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Diarrhö  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("11", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Gastrogastrale Fistel" ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("12", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Ulcusperforation  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("13", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Chronische Ulcus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("14", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Chronische Schmerzen  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("15", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Ileus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("16", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Choledocholithiasis nach Magen-Bypass  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("17", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Leakage  " ELSE CONCAT("", "") END, "NULL")
                 ) AS "Indikation zur Revision",

The result is displayed in this screenshot:

enter image description here

The only thing which remains to do now is a nice formatting (do the commata and blank spaces away etc.).

Issue solved.

mtjmohr
  • 99
  • 9
  • If you remove the `COALESCE` and the `ELSE` condition on your `CASE` statements, all the extra commas and blank spaces will go away. – Nick May 05 '18 at 06:59
  • Please mark your answer accepted so that other users can find it. Also delete the earlier answer. – Nick May 05 '18 at 07:00
  • @Nick: Please indicate where I can do this. – mtjmohr May 05 '18 at 13:37
  • Underneath the voting buttons (at the top left of your answer) is a little tick mark. Clicking on that will accept the answer. At the bottom of your other answer (in very small text) it will say "share edit delete flag", clicking on the delete will remove the post. – Nick May 05 '18 at 22:13
  • Btw did you try my suggestion about removing the `COALESCE` and `ELSE` clauses? – Nick May 05 '18 at 22:18
  • @Nick: Thank you, I found it, but am allowed to do this only by tomorrow. I will test your suggestion to remove COALESCE and ELSE again since I have tried that before already - it worked. Thank you here again. – mtjmohr May 05 '18 at 22:31