mysql获取第一个分隔符_MySQL-获取前3个逗号分隔的值

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.

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.

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).

Any advice appreciated.

Thanks.

解决方案

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) will return aaaaa, bbbbb

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

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

Please see fiddle here.

Edit

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

Please see fiddle here.