从自由文本字段中提取数字并将“k”替换为“000”的功能-解网

问：

我的数据中有一个自由文本列，其中包含单词和数字。我正在使用 Microsoft SQL Server。

例如：“Johnny 收到了 1,000 件产品。

我只想从此列中提取数字，我已经成功地使用以下功能完成了这些操作：

CREATE FUNCTION getNumericValue (@inputString varchar(500))
RETURNS varchar(500)
AS 
BEGIN
    DECLARE @integerPart INT
    SET @integerPart = PATINDEX('%[^0-9]%', @inputString)

    BEGIN 
        WHILE @integerPart > 0
        BEGIN 
            SET @inputString = STUFF(@inputString, @integerPart, 1, '')
            SET @integerPart = PATINDEX('%[^0-9]%', @inputString)
        END
    END 

    RETURN ISNULL(@inputString, 0)
END
GO

但是有一些使用单位的观察结果，例如：“Johnny 收到了 1k 产品。有没有办法修改这个函数，当有一个数字附加了“k”时，它会用“000”替换k，或者如果有一个附加了“m”的数字，它会用“000000”替换m？

还有一些观察结果包括句子末尾的日期，这些日期应该被排除在外 - 只应包括找到的第一个数字。

SQL Server 函数

这听起来像是问题出在插入数据和设计上。这些都应插入到不同的列中。事实上，你有一个只在评论中突出显示的场景，这也不会使这变得更容易。我怀疑，如果你“必须”这样做（而不是修复你的设计），那么 T-SQL 就不是你的朋友。

0赞 amatof 9/1/2023

@ThomA 不幸的是，我无法控制数据的插入方式，也无法更改

答：

2赞 Stu 9/1/2023 #1

您可以尝试以下函数，该函数利用内置的翻译函数去除非数字字符，然后使用一些 patindex 检查和一些大小写表达式逻辑来应用您的“k”或“m”乘法器：

create or alter function dbo.getNumericValue (@inputString Varchar(500))
returns Varchar(20)
as 
begin
  return (
    select Try_Convert(Int, Replace(Translate(s,'ABCDEFGHIJKLMNOPQRSTUVWXYZ.,',Replicate('*',28)), '*','') 
      * Choose(case c when 'k' then 2 when 'm' then 3 else 1 end, 1, 1000, 1000000))
    from (select @inputString s)s
    cross apply(values( NullIf(PatIndex('%[0-9][mk] %', s), 0)))p(p)
    cross apply(values(Substring(s, p + 1, 1)))c(c)
  );
end;

根据源数据，您可以向列表中添加任何其他必需的标点符号。

如果针对一大组行运行，我将使用表值函数而不是标量。

观看演示 Fiddle

上一个：运算符不存在：字符 varying[] = 文本

下一个：将 Case 语句创建为函数

从自由文本字段中提取数字并将“k”替换为“000”的功能

Function to pull numbers from free text field and replace 'k' with '000'

评论