MySQL-获取前3个逗号分隔的值 [英] MySQL - Get first 3 comma separated values

问题描述

我有一个查询，该查询返回带有一组逗号分隔值的字段.我想将这些值的前三个放入查询结果的单独列中.

I have a query which returns a field with a set of comma separated values. I would like to get the first three of these values into separate columns in the query result.

我可以使用SUBSTRING_INDEX获得第一个，但是我如何获得另外两个?

I can get the first, using SUBSTRING_INDEX but how can I get the other two?

SELECT 
  'aaaaa, bbbbb, ccccc',
  SUBSTRING_INDEX('aaaaa, bbbbb, ccccc', ',', 1) AS column_one

编辑-糟糕，抱歉忘了提及.我要拆分的值可能要比三个字符串多(或少).

EDIT - Oops, sorry forgot to mention. The value I want to split could have more (or less) than three strings to extract.

例如，上面的字符串可以很容易地是"aaaaa"或"aaaaa，bbbbb，ccccc，ddddd，eeeee".

For example, the above string could easily be 'aaaaa' or 'aaaaa, bbbbb, ccccc, ddddd, eeeee'.

在每种情况下，我只需要前三个(或存在三个).

In each case, I only need the first three (or however many exist).

任何建议表示赞赏.

谢谢.

推荐答案

您可以使用

You could use SUBSTRING_INDEX twice, the second one with -1 parameter:

SELECT
  'aaaaa, bbbbb, ccccc',
  SUBSTRING_INDEX('aaaaa, bbbbb, ccccc', ',', 1) AS column_one,
  SUBSTRING_INDEX(SUBSTRING_INDEX('aaaaa, bbbbb, ccccc', ',', 2), ',', -1) AS column_two,
  SUBSTRING_INDEX(SUBSTRING_INDEX('aaaaa, bbbbb, ccccc', ',', 3), ',', -1) AS column_three

如果参数为负，则返回最终定界符右侧的所有内容(从右侧开始计数).例如

If the parameter is negative, everything to the right of the final delimiter (counting from the right) is returned. Eg.

• SUBSTRING_INDEX('aaaaa, bbbbb, ccccc', ',', 2)将返回aaaaa, bbbbb
• SUBSTRING_INDEX( aaaaa，bbbbb , ',', -1)然后将返回bbbbb

• SUBSTRING_INDEX('aaaaa, bbbbb, ccccc', ',', 2) will return aaaaa, bbbbb
• SUBSTRING_INDEX(aaaaa, bbbbb, ',', -1) will then return bbbbb

您可能还想使用'，'作为分隔符，或 TRIM 结果.

You also might want to use ', ' as a delimiter, or TRIM the result.

请在此处看到小提琴.

修改

如果您想考虑可能少于三个值的字符串，则可以使用以下内容:

If you want to consider strings that might have less than three values, you could use something like this:

SELECT
  s,
  SUBSTRING_INDEX(s, ',', 1) AS column_one,
  CASE WHEN LENGTH(s)-LENGTH(Replace(s, ',', ''))>0
       THEN SUBSTRING_INDEX(SUBSTRING_INDEX(s, ',', 2), ',', -1)
       ELSE NULL END AS column_two,
  CASE WHEN LENGTH(s)-LENGTH(Replace(s, ',', ''))>1
       THEN SUBSTRING_INDEX(SUBSTRING_INDEX(s, ',', 3), ',', -1)
       ELSE NULL END AS column_three
FROM
  strings

请在此处看到小提琴.

这篇关于MySQL-获取前3个逗号分隔的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！