从现有的 sql 数据中提取美元金额? [英] Extracting dollar amounts from existing sql data?

问题描述

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

I have a field with that contains a mix of descriptions and dollar amounts. With TSQL, I would like to extract those dollar amounts, then insert them into a new field for the record.

-- 更新 --

一些数据样本可能是:

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.

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

In the set above I was thinking of just grabbing the first occurrence of the dollar figure... that is the simplest.

我不想从原始文本中删除金额，只是获取它们的值，然后将它们添加到新字段中.

I'm not trying to remove the amounts from the original text, just get their value, and add them to a new field.

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

The amounts could/could not contain decimals, and commas.

我确信 PATINDEX 不会削减它，而且我不需要一个非常正则表达式的函数来完成这个.

I'm sure PATINDEX won't cut it and I don't need an extremely RegEx function to accomplish this.

但是，查看 OLE Regex Find (Execute) 函数 here，似乎是最健壮的，但是在尝试使用该功能时，我在 SSMS 中收到以下错误消息:

However, looking at The OLE Regex Find (Execute) function here, appears to be the most robust, however when trying to use the function I get the following error message in SSMS:

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

SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

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

I don't want to go and changing my server settings just for this function. I have another regex function that works just fine without changes.

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

I can't imagine this being that complicated to just extract dollar amounts. Any simpler ways?

谢谢.

推荐答案

    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

这篇关于从现有的 sql 数据中提取美元金额?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！