修改SQL函数以提取带括号的特定单词前的数字（含浮点数）

场景与需求 #

• 数据示例：

  String 1: 'Random Text 3 Random 568 Text 5.5 Test Random Text 345'
  String 2: 'Random Text 3 Test Text Random'
  String 3: 'Random Text 777 Random Text'
  String 4: 'Random Text (3.9 Test) Text Random'
  

• 预期输出：

  String 1: '5.5'
  String 2: '3'
  String 3: 无输出
  String 4: '3.9'
  

• 原函数问题：当字符串包含括号包裹的浮点数时，无法正确提取目标数字

修改后的函数实现 #

CREATE FUNCTION GetNumberBeforeStringTest
(
    @stringToParse varchar(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
    -- 忽略大小写匹配'test'
    DECLARE @testIndex INT = PATINDEX('%test%', LOWER(@stringToParse));
    
    IF @testIndex = 0 RETURN NULL;

    -- 截取'Test'之前的子串
    DECLARE @preTestStr VARCHAR(100) = TRIM(SUBSTRING(@stringToParse, 0, @testIndex));
    
    -- 从后往前定位数字（含浮点数、括号内数字）的起始位置
    DECLARE @startPos INT = 0;
    DECLARE @currentPos INT = LEN(@preTestStr);
    
    WHILE @currentPos > 0
    BEGIN
        DECLARE @char CHAR(1) = SUBSTRING(@preTestStr, @currentPos, 1);
        -- 匹配数字、小数点，或左括号（处理括号包裹场景）
        IF (@char BETWEEN '0' AND '9') OR @char = '.' OR @char = '('
        BEGIN
            @startPos = @currentPos;
            -- 遇到左括号则停止，括号内即为目标数字段
            IF @char = '('
                BREAK;
        END
        ELSE
        BEGIN
            -- 已找到数字起始位，遇到非目标字符就终止遍历
            IF @startPos > 0
                BREAK;
        END
        SET @currentPos = @currentPos - 1;
    END
    
    IF @startPos = 0 RETURN NULL;
    
    -- 提取候选数字串，移除左括号并修剪空格
    DECLARE @candidateStr VARCHAR(100) = TRIM(REPLACE(SUBSTRING(@preTestStr, @startPos, LEN(@preTestStr) - @startPos + 1), '(', ''));
    
    -- 验证是否为有效十进制数
    IF TRY_CAST(@candidateStr AS decimal(18,2)) IS NULL
        RETURN NULL;

    RETURN @candidateStr;
END
GO

关键修改点 #

1. 大小写兼容：用LOWER()统一字符串大小写，避免因Test/TEST等不同写法导致匹配失败
2. 括号场景适配：遍历过程中识别左括号，直接定位括号内的数字段
3. 浮点数支持：保留小数点的匹配逻辑，确保能提取带小数的数字
4. 精准定位：替换原反向截取逻辑，改为从后向前遍历，避免空格分隔带来的误判

测试验证 #

-- 执行以下语句验证结果
SELECT dbo.GetNumberBeforeStringTest('Random Text 3 Random 568 Text 5.5 Test Random Text 345') AS String1; -- 返回 '5.5'
SELECT dbo.GetNumberBeforeStringTest('Random Text 3 Test Text Random') AS String2; -- 返回 '3'
SELECT dbo.GetNumberBeforeStringTest('Random Text 777 Random Text') AS String3; -- 返回 NULL
SELECT dbo.GetNumberBeforeStringTest('Random Text (3.9 Test) Text Random') AS String4; -- 返回 '3.9'

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