修改SQL Server自定义函数：提取首数字并替换k/m、排除日期

需求说明 #

原函数已能从自由文本（如Johnny received 1,000 products.）提取数字，现需新增两个功能：

• 识别数字后的k/m后缀，分别替换为000/000000（例如2.5k转为2500，1.2m转为1200000）
• 排除文本末尾的日期内容，仅提取首个出现的有效数字

修改方案 #

核心逻辑 #

1. 清理末尾日期：先移除文本末尾常见格式的日期（如MM/DD/YYYY、DD-MM-YYYY等）
2. 提取首个数字段：从清理后的文本中定位第一个包含数字、逗号、小数点、k/m的连续片段
3. 单位转换与数值化：替换k/m为对应0的数量，清理千位分隔符后转为数值类型

修改后的函数代码 #

CREATE OR ALTER FUNCTION dbo.getNumericValue(@InputText NVARCHAR(MAX))
RETURNS DECIMAL(18,6)
AS
BEGIN
    DECLARE @CleanedText NVARCHAR(MAX)
    DECLARE @FirstNumericSegment NVARCHAR(100)
    DECLARE @Result DECIMAL(18,6) = 0

    -- 步骤1：移除文本末尾的常见格式日期，可按需扩展规则
    SET @CleanedText = CASE 
        WHEN @InputText LIKE '%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN LEFT(@InputText, LEN(@InputText)-10)
        WHEN @InputText LIKE '%[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN LEFT(@InputText, LEN(@InputText)-10)
        WHEN @InputText LIKE '%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' THEN LEFT(@InputText, LEN(@InputText)-10)
        ELSE @InputText
    END

    -- 步骤2：定位并提取首个数字+单位片段
    DECLARE @Pos INT = PATINDEX('%[0-9][0-9,.]*[km]?%', @CleanedText)
    IF @Pos > 0
    BEGIN
        SET @FirstNumericSegment = SUBSTRING(@CleanedText, @Pos, PATINDEX('%[^0-9,.km]%', SUBSTRING(@CleanedText, @Pos, LEN(@CleanedText)) + ' ') - 1)
        
        -- 步骤3：处理单位转换与分隔符清理
        SET @FirstNumericSegment = REPLACE(@FirstNumericSegment, ',', '')
        SET @FirstNumericSegment = CASE
            WHEN @FirstNumericSegment LIKE '%k' THEN REPLACE(@FirstNumericSegment, 'k', '000')
            WHEN @FirstNumericSegment LIKE '%m' THEN REPLACE(@FirstNumericSegment, 'm', '000000')
            ELSE @FirstNumericSegment
        END

        -- 转换为数值，转换失败时返回0
        SET @Result = TRY_CAST(@FirstNumericSegment AS DECIMAL(18,6))
    END

    RETURN @Result
END

测试案例 #

补充说明 #

• 日期匹配规则可根据实际业务使用的日期格式扩展（如新增MMM DD, YYYY格式判断）
• 若需支持大写K/M，可在代码中加入UPPER()转换统一处理
• TRY_CAST确保转换失败时返回0，避免函数抛出异常

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