SQL如何提取字符串中任意位置的数值子串

方案1：修改现有函数实现任意位置数值提取 #

如果你使用的是MySQL 8.0及以上版本，可以直接用内置正则匹配函数改写，不需要依赖固定分隔符，适配任意位置的浮点数提取：

FUNCTION `ddbb`.`extract_num`(
    `x` VARCHAR(255)
) RETURNS varchar(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
    READS SQL DATA
BEGIN
-- 匹配第一个出现的 数字+.+数字 格式的浮点数，如需匹配整数可以调整正则为 '[0-9]+(\.[0-9]+)?'
RETURN REGEXP_SUBSTR(x, '[0-9]+\.[0-9]+');
END

如果是更低版本的MySQL不支持正则函数，可以用遍历字符的逻辑改写，筛选出所有数字和小数点字符拼接返回即可：

FUNCTION `ddbb`.`extract_num`(
    `x` VARCHAR(255)
) RETURNS varchar(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
    READS SQL DATA
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE res VARCHAR(255) DEFAULT '';
DECLARE c CHAR(1);
WHILE i <= LENGTH(x) DO
    SET c = SUBSTRING(x, i, 1);
    IF c REGEXP '[0-9.]' THEN
        SET res = CONCAT(res, c);
    END IF;
    SET i = i + 1;
END WHILE;
RETURN res;
END

方案2：保留现有函数不变，查询时直接实现 #

针对你给出的两种样例格式，数值都在空格分隔的最后一段，可以直接计算总段数后取最后一段即可：

-- 此处替换为你原函数的实际名称，示例中原函数名为split_str
SELECT 
    split_str(目标字段名, ' ', LENGTH(目标字段名) - LENGTH(REPLACE(目标字段名, ' ', '')) + 1) AS 提取数值
FROM 表名;

如果数值不一定在最后，MySQL8.0+可直接在查询中调用正则函数，不需要调用你的拆分函数：

SELECT REGEXP_SUBSTR(目标字段名, '[0-9]+\.[0-9]+') AS 提取数值 FROM 表名;

内容的提问来源于stack exchange，提问作者fqv572