MySQL如何提取连字符后首字母并拼接成新列？

针对无固定长度、以连字符-分隔的字符串，要提取首字符及每个分隔符后的首字符并拼接，可通过以下两种方案实现：

方案一：自定义函数（兼容所有MySQL版本） #

创建自定义函数遍历字符串，收集目标字符：

DELIMITER //
CREATE FUNCTION get_initial_str(input_str VARCHAR(255)) 
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE result_str VARCHAR(255) DEFAULT '';
    DECLARE current_pos INT DEFAULT 1;
    DECLARE char_len INT DEFAULT LENGTH(input_str);
    
    -- 取第一个字符
    SET result_str = CONCAT(result_str, SUBSTRING(input_str, 1, 1));
    
    WHILE current_pos <= char_len DO
        IF SUBSTRING(input_str, current_pos, 1) = '-' AND current_pos < char_len THEN
            -- 取连字符后的第一个字符
            SET result_str = CONCAT(result_str, SUBSTRING(input_str, current_pos + 1, 1));
        END IF;
        SET current_pos = current_pos + 1;
    END WHILE;
    
    RETURN result_str;
END //
DELIMITER ;

使用示例：

-- 测试数据
SELECT get_initial_str('asdaff-asarger-adsf-tggdg') AS result; -- 输出 'aaat'
SELECT get_initial_str('jkl-jkui-kmj-iop-kiu') AS result; -- 输出 'jkjkki'

方案二：递归CTE（MySQL 8.0及以上版本） #

无需创建函数，用递归公共表表达式拆分字符串并提取首字母：

WITH RECURSIVE str_parts AS (
    SELECT 
        input_str,
        SUBSTRING(input_str, 1, 1) AS initial,
        LOCATE('-', input_str) AS dash_pos
    FROM your_table
    UNION ALL
    SELECT
        SUBSTRING(input_str, dash_pos + 1),
        SUBSTRING(SUBSTRING(input_str, dash_pos + 1), 1, 1),
        LOCATE('-', SUBSTRING(input_str, dash_pos + 1))
    FROM str_parts
    WHERE dash_pos > 0
)
SELECT 
    GROUP_CONCAT(initial ORDER BY (SELECT NULL) SEPARATOR '') AS result
FROM str_parts;

替换your_table为实际表名，若要处理单条字符串，可将初始查询改为：

WITH RECURSIVE str_parts AS (
    SELECT 
        'asdaff-asarger-adsf-tggdg' AS input_str,
        SUBSTRING('asdaff-asarger-adsf-tggdg', 1, 1) AS initial,
        LOCATE('-', 'asdaff-asarger-adsf-tggdg') AS dash_pos
    UNION ALL
    SELECT
        SUBSTRING(input_str, dash_pos + 1),
        SUBSTRING(SUBSTRING(input_str, dash_pos + 1), 1, 1),
        LOCATE('-', SUBSTRING(input_str, dash_pos + 1))
    FROM str_parts
    WHERE dash_pos > 0
)
SELECT GROUP_CONCAT(initial ORDER BY (SELECT NULL) SEPARATOR '') AS result; -- 输出 'aaat'

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