regex - Extracting dollar amounts from existing sql data?

Question

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.

-- UPDATE --

Some data samples could be:

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.

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

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 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?

Thanks.

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