从现有 sql 数据中提取美元金额？

Question

我有一个包含描述和美元金额混合的字段。使用 TSQL，我想提取这些美元金额，然后将它们插入到新的记录字段中。

-- 更新 --

一些数据样本可能是：

Used knife set for sale $200.00 or best offer.
$4,500 Persian rug for sale.
Today only, $100 rebate.
Five items for sale: $20 Motorola phone car charger, $150 PS2, $50.00 3 foot high shelf.

在上面的集合中，我想只抓取第一次出现的美元数字......这是最简单的。

我并不是试图从原始文本中删除金额，只是获取它们的值，并将它们添加到新字段中。

金额可以/不可以包含小数点和逗号。

我确信 PATINDEX 不会削减它，并且我不需要非常正则表达式函数来完成此任务。

但是，查看 OLE 正则表达式查找（执行）函数 此处似乎是最强大的，但是当尝试使用该函数时，我在 SSMS 中收到以下错误消息：

SQL Server 阻止访问组件的过程“sys.sp_OACreate” “Ole Automation procedures”，因为该组件已关闭 该服务器安全配置的一部分。一个系统 管理员可以通过以下方式启用“Ole Automation procedures” 使用 sp_configure。有关启用“Ole”的更多信息 自动化过程，请参阅 SQL Server 中的“外围区域配置” 在线书籍。

我不想仅仅为了这个功能而改变我的服务器设置。我有另一个正则表达式函数，无需更改即可正常工作。

我无法想象仅提取美元金额会如此复杂。有没有更简单的方法？

谢谢。

Answer 1

    CREATE FUNCTION dbo.fnGetAmounts(@str nvarchar(max))
    RETURNS TABLE 
    AS
    RETURN 
    (
    -- generate all possible starting positions ( 1 to len(@str))
    WITH StartingPositions AS
    (
        SELECT 1 AS Position
        UNION ALL
        SELECT Position+1
        FROM StartingPositions
        WHERE Position <= LEN(@str)
    )
   -- generate possible lengths
    , Lengths AS
    (
        SELECT 1 AS [Length]
        UNION ALL
        SELECT [Length]+1
        FROM Lengths
        WHERE [Length] <= 15
    )
    -- a Cartesian product between StartingPositions and Lengths
    -- if the substring is numeric then get it
    ,PossibleCombinations AS 
    (

         SELECT CASE                
                WHEN ISNUMERIC(substring(@str,sp.Position,l.Length)) = 1 
                   THEN substring(@str,sp.Position,l.Length)         
                 ELSE null END as Number
                 ,sp.Position
                 ,l.Length
         FROM StartingPositions sp, Lengths l           
         WHERE sp.Position <= LEN(@str)            
    )
-- get only the numbers that start with Dollar Sign, 
-- group by starting position and take the maximum value 
-- (ie, from $, $2, $20, $200 etc)
    SELECT MAX(convert(money, Number)) as Amount
    FROM PossibleCombinations
    WHERE Number like '$%' 
    GROUP BY Position
    )

    GO

    declare @str nvarchar(max) = 'Used knife set for sale $200.00 or best offer.
    $4,500 Persian rug for sale.
    Today only, $100 rebate.
    Five items for sale: $20 Motorola phone car charger, $150 PS2, $50.00 3 foot high shelf.'

    SELECT *
    FROM dbo.fnGetAmounts(@str)
    OPTION(MAXRECURSION 32767) -- max recursion option is required in the select that uses this function

Answer 2

这个链接应该有帮助。

http://blogs.lessthandot.com/index .php/DataMgmt/DataDesign/extracting-numbers-with-sql-server

假设您可以提取数字，无论是否有 $ 符号。如果这是一个严格的要求，则需要一些模组。