MySQL查询:如何使用CONCAT_WS和COALESCE正确识别逗号分隔的结果值并将其重新转换为原始概念 [英] MySQL query: How to properly identify and retranslate comma-separated result values to the original notions using CONCAT_WS and COALESCE
本文介绍了MySQL查询:如何使用CONCAT_WS和COALESCE正确识别逗号分隔的结果值并将其重新转换为原始概念的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
关于将基于字符串的结果以逗号分隔的方式保存在MySQL数据库表中的讨论很多.我不想在这里用我自己的哲学评论来扩展这个范围，我只是想说这个问题对我来说是众所周知的，但现在不适合这个话题.
主题是我有这种情况要评估.数据另存为基于字符串的密码.这些密码中的每一个都表示在执行特定类型的手术后的医学并发症.
示例:
MySQL数据库表"complications"包含一个名为手术修订指示"(VARCHAR [50])的字段，在该字段中保存了诸如"3、7、9、16"之类的数据条目，因为这些是从多个选择菜单中选择的用户选择了4种不同的适应症.
现在我要执行以下操作:
我知道"3"，"7"，"9"和"16"代表什么.我想使用MySQL SELECT查询将这些密码重新显示为它们的原始概念(例如重量恢复"，减肥失败"等).
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",
您现在可能会问:为什么这个人那么旁观?"或为什么不使用PHP呢?".我使用PHP进行操作，但使用的是其他上下文.在这里，我需要使用SQL查询进行直接评估，因为在此代码中，并非我所有的数据都被提取出来，以便在Excel CSV文件中自动创建要二次处理的结果集-我不想重新发明轮子
现在，上述查询没有执行我想要的操作.在我的示例中，我想从此"3、7、9、16"字符串中显示体重增加，反流，营养不良，胆道结石病".
我知道这样的事实，例如...％3％" ...在这里并不能解决问题.
有一些建议使用"FIND_IN_SET('3'，op.OP2RevisionIndikation)"，但是这里我们不是直接在主要的SELECT语句中，而是在使用COALESCE和CONCAT_WS的CASE WHEN THEN ELSE END过程中.
您有没有人知道如何通过将字符串"3、7、9、16"映射到原始值来正确评估该字符串，并让MySQL以这种方式显示它?
我希望我已经足够透彻并且可以理解.
最诚挚的问候，并无限感谢您的帮助
马库斯
解决方案
在更常见的情况下，当目标是将逗号分隔的正整数值列表翻译"为对应的字符串列表时，在与原始值列表相同的顺序，并且没有引入多余的逗号"和空格...
我们可以运用可怕的表情来实现这一目标.
作为演示.
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
返回
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
^^ ^ ^ ^^
注意事项:
每行ELT是一个 exact 副本，除了一个整数，该整数基本上指定要提取逗号分隔的整数列表中的第n个元素，第1个，第4个，第5个，等
表达式只处理列表中有限数量的元素；每个元素都需要一个单独的ELT表达式.该示例处理逗号分隔列表中的多达20个元素.可以扩展以处理更多元素.
仅对于逗号分隔的正整数列表，此选项仅"有效.逗号之间的每个值都将被评估为整数.这意味着元素'x17'将求值为0.元素'-6.2'将计算为-6.元素
'007jamesbond'将评估为7.依此类推.
从评估中获取的整数值用作在ELT函数中从字符串列表中查找"字符串的索引.
计算得出的整数值与列表中的字符串不对应的元素(例如0，-6)将被忽略，就像原始列表中没有该元素一样.
在示例中返回的最后两行显示了用逗号分隔的列表的行为，我们可能认为它们是畸形的.翻译表达式是尽力而为"，它返回可以翻译的内容.逗号分隔的列表格式不正确时，该表达式不会引发错误，也不会返回NULL或空字符串.
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.
这篇关于MySQL查询:如何使用CONCAT_WS和COALESCE正确识别逗号分隔的结果值并将其重新转换为原始概念的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
