mysql - 如何编写选择可变长度子字符串的 SQL 查询

Question

我目前正在处理由以下格式的字符串组成的数据:

"{Weight: 250g}{Color: Red}{Variety: Honeycrisp}{Expiration: Jun 15}"

此数据目前在一列中，但我正在尝试解析数据，以便最终结果是 4 个单独的列(重量、颜色、品种和有效期)。我尝试使用 SUBSTRING() 函数，但遇到了问题，因为每个部分的长度因数据行而异。您会建议使用不同的功能吗？

Answer 1

以下是针对 MySQL 的。基于https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('{Weight: 250g}{Color: Red}{Variety: Honeycrisp}{Expiration: Jun 15}', '}{Color: ', 1), 'Weight: ', -1) as Weight;

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('{Weight: 250g}{Color: Red}{Variety: Honeycrisp}{Expiration: Jun 15}', '}{Variety: ', 1), 'Color: ', -1) as Color;

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('{Weight: 250g}{Color: Red}{Variety: Honeycrisp}{Expiration: Jun 15}', '}{Expiration: ', 1), 'Variety: ', -1) as Variety;

SELECT TRIM(TRAILING '}' from SUBSTRING_INDEX('{Weight: 250g}{Color: Red}{Variety: Honeycrisp}{Expiration: Jun 15}', 'Expiration: ', -1))as Expiration;

基于此，可以更轻松地编写一个函数来插入到其他表或提取这些值。

DELIMITER $$
DROP FUNCTION IF EXISTS ExtractKeyValues $$
CREATE FUNCTION ExtractKeyValues(input text, Key_ varchar(50)) RETURNS VARCHAR(10)
    DETERMINISTIC
BEGIN
    DECLARE res varchar(50);

    Case 
        when key_ = 'Weight' THEN
            SET res = SUBSTRING_INDEX(SUBSTRING_INDEX(input, '}{Color: ', 1), 'Weight: ', -1);
        when Key_ = 'Color' THEN
            SET res = SUBSTRING_INDEX(SUBSTRING_INDEX(input, '}{Variety: ', 1), 'Color: ', -1);
        when key_ = 'Variety' THEN
            SET res = SUBSTRING_INDEX(SUBSTRING_INDEX(input, '}{Expiration: ', 1), 'Variety: ', -1);
        when key_ = 'Expiration' THEN
            SET res = TRIM(TRAILING '}' from SUBSTRING_INDEX(input, 'Expiration: ', -1));
    END CASE;

    RETURN (res);
END; $$
Delimiter ;

要使用它，我们可以这样做:

select ExtractKeyValues('{Weight: 250g}{Color: Red}{Variety: Honeycrisp}{Expiration: Jun 15}', 'Weight');
select ExtractKeyValues('{Weight: 250g}{Color: RED}{Variety: Honeycrisp}{Expiration: Jun 15}', 'Color');
select ExtractKeyValues('{Weight: 250g}{Color: Red}{Variety: Honeycrisp}{Expiration: Jun 15}', 'Variety');
select ExtractKeyValues('{Weight: 250g}{Color: Red}{Variety: Honeycrisp}{Expiration: Jun 15}', 'Expiration');