使用分隔符提取 MySQL 子串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34992575/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL substring extraction using delimiter
提问by Bibin Jose
I want to extract the substrings from a string in MySQL. The string contains multiple substrings separated by commas(','). I need to extract these substrings using any MySQL functions.
我想从 MySQL 中的字符串中提取子字符串。该字符串包含多个以逗号（'，'）分隔的子字符串。我需要使用任何 MySQL 函数提取这些子字符串。
For example:
例如：
Table Name: Product
-----------------------------------
item_code name colors
-----------------------------------
102 ball red,yellow,green
104 balloon yellow,orange,red
I want to select the colors field and extract the substrings as red, yellow and green as separated by comma.
我想选择颜色字段并将子字符串提取为以逗号分隔的红色、黄色和绿色。
回答by Sameer Mirji
A possible duplicate of this: Split value from one field to two
一个可能的重复：将值从一个字段拆分为两个
Unfortunately, MySQL does not feature a split string function. As in the link above indicates there are User-defined Split function.
不幸的是，MySQL 没有拆分字符串功能。如上面的链接所示，有User-defined Split function。
A more verbose version to fetch the data can be the following:
获取数据的更详细的版本如下：
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 1), ',', -1) as colorfirst,
SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 2), ',', -1) as colorsecond
....
SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', n), ',', -1) as colornth
FROM product;
回答by Kshitij Satpute
回答by Ulad Kasach
Based on https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/, here is a way to access a value from a delimiter separated array:
基于https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/，这是一种从分隔符分隔的数组中访问值的方法：
/*
usage:
SELECT get_from_delimiter_split_string('1,5,3,7,4', ',', 1); -- returns '5'
SELECT get_from_delimiter_split_string('1,5,3,7,4', ',', 10); -- returns ''
*/
CREATE FUNCTION get_from_delimiter_split_string(
in_array varchar(255),
in_delimiter char(1),
in_index int
)
RETURNS varchar(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
RETURN REPLACE( -- remove the delimiters after doing the following:
SUBSTRING( -- pick the string
SUBSTRING_INDEX(in_array, in_delimiter, in_index + 1), -- from the string up to index+1 counts of the delimiter
LENGTH(
SUBSTRING_INDEX(in_array, in_delimiter, in_index) -- keeping only everything after index counts of the delimiter
) + 1
),
in_delimiter,
''
);
here are the docs for the string operators for reference: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html
以下是字符串运算符的文档供参考：https: //dev.mysql.com/doc/refman/8.0/en/string-functions.html
