你可以使用SUBSTRING_INDEX http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index两次,第二次带有 -1 参数:
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
如果参数为负数,则返回最终分隔符右侧(从右侧算起)的所有内容。例如。
-
SUBSTRING_INDEX('aaaaa, bbbbb, ccccc', ',', 2)
将返回aaaaa, bbbbb
-
SUBSTRING_INDEX(
啊啊啊啊啊, ',', -1)
然后将返回bbbbb
您可能还想使用 ', ' 作为分隔符,或者TRIM http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_trim结果。
请参阅小提琴here http://sqlfiddle.com/#!2/d41d8/9654.
Edit
如果您想考虑可能少于三个值的字符串,您可以使用如下所示的内容:
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
请参阅小提琴here http://sqlfiddle.com/#!2/5e054/1.