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
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.
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:
The only thing which remains to do now is a nice formatting (do the commata and blank spaces away etc.).
Issue solved.
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://i.stack.imgur.com/ryiMO.jpg', '推荐 做个烂人 的文章《MySQL query: How to properly identify and retransl》','https://www.manongdao.com/article-924046.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}