更新时间:2023-02-26 10:12:04
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
请在此处看到小提琴.