如何编写SQL函数查找键值对指定键并返回对应值

嘿，这个需求我之前也碰到过，刚好可以给你详细拆解怎么实现。我们的目标是写一个SQL函数，输入要查找的键和键值对字符串，返回对应的值或者空值。下面分常用数据库给你示例：

MySQL版本实现 #

核心思路是给键值对字符串前后加逗号，避免边界匹配错误（比如键在开头或结尾的情况），然后精准定位目标键的位置，再截取对应的值。

DELIMITER //
CREATE FUNCTION find_kv_value(p_key INT, p_kv_str VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    -- 给字符串前后拼接逗号，处理边界场景
    SET @kv_with_bounds = CONCAT(',', p_kv_str, ',');
    -- 拼接目标键的完整匹配模式，确保不会误匹配类似"14:"的情况
    SET @search_pattern = CONCAT(',', p_key, ':');
    
    -- 查找模式的起始位置
    SET @pos = LOCATE(@search_pattern, @kv_with_bounds);
    
    IF @pos = 0 THEN
        -- 未找到目标键，返回NULL
        RETURN NULL;
    END IF;
    
    -- 计算值的起始位置：模式结束的位置
    SET @value_start = @pos + LENGTH(@search_pattern);
    -- 找到值后面的逗号位置，确定值的结束点
    SET @value_end = LOCATE(',', @kv_with_bounds, @value_start);
    
    -- 截取对应的值并返回
    RETURN SUBSTRING(@kv_with_bounds, @value_start, @value_end - @value_start);
END //
DELIMITER ;

测试验证 #

调用函数看看效果：

-- 找到键4，返回对应值23
SELECT find_kv_value(4, '3:66,8:54,4:23');
-- 找不到键5，返回NULL
SELECT find_kv_value(5, '3:66,8:54,4:23');

SQL Server版本实现 #

如果用的是SQL Server，核心逻辑一致，只是换用对应的字符串函数：

CREATE FUNCTION dbo.find_kv_value(@p_key INT, @p_kv_str VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
    DECLARE @kv_with_bounds VARCHAR(255) = ',' + @p_kv_str + ',';
    DECLARE @search_pattern VARCHAR(255) = ',' + CAST(@p_key AS VARCHAR) + ':';
    DECLARE @pos INT = CHARINDEX(@search_pattern, @kv_with_bounds);
    
    IF @pos = 0
        RETURN NULL;
    
    DECLARE @value_start INT = @pos + LEN(@search_pattern);
    DECLARE @value_end INT = CHARINDEX(',', @kv_with_bounds, @value_start);
    
    RETURN SUBSTRING(@kv_with_bounds, @value_start, @value_end - @value_start);
END;

额外注意点 #

• 如果键值对字符串里有空格（比如3:66, 8:54），需要先通过REPLACE(p_kv_str, ' ', '')去掉空格再处理，避免匹配失败。
• 如果键值对存在重复键（比如4:12,4:23），当前函数会返回第一个匹配的值；如果需要返回最后一个，可以通过反转字符串的方式调整查找逻辑。

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