SQL Server: extracting $#,### from a string

Question

I'm trying to extract a dollar value from a string using a mixture of substrings, charindexes, and patindexes. I can seem to extract the $###,### pattern from all string except when it falls at the end of the string.

Here is some code with test cases:

CREATE TABLE #TMP 
(
    string VARCHAR(50)
)
GO

INSERT INTO #TMP
VALUES ('I have $4,000'),
       ('$44,450is what I have'),
       ('this $600 is what I have now'),
       ('$5 cause I am broke'),
       ('I have $10,000,000,000 '),
       ('No Money Here')
GO

SELECT 
    *,
    SUBSTRING(string, 
              CHARINDEX('$', string), 
              PATINDEX('%[^0-9,]%', SUBSTRING(string, CHARINDEX('$', string) + 1, 80000))) AS Result
FROM 
    #TMP

DROP TABLE #TMP
GO

Results:

SQL Server version:

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)

I'm using Version Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) — Richard Wolff, Mar 15 '19 at 14:01
Hi i doesn't work because your $4.000 it's at the end off line i think it's way for looking — Sanpas, Mar 15 '19 at 14:09

score 3 · Accepted Answer · answered Mar 15 '19 at 14:19

3

Just add an x (or any non-numeric character) to every instance of string:

SELECT 
    *,
    SUBSTRING(
        string + 'x', 
        CHARINDEX('$',string + 'x'), 
        PATINDEX('%[^0-9,]%',SUBSTRING(string + 'x',CHARINDEX('$',string + 'x')+1, 80000))
    ) AS Result
FROM 
    #TMP

answered Mar 15 '19 at 14:19

Aaron Bertrand

272,866
37
466
490

1

Simple. What would be more efficient? The case statement or adding the x? – Richard Wolff Mar 15 '19 at 15:56
I feel like using `LEN()` would be worse for performance but probably not in any human-measurable way. – Aaron Bertrand Mar 15 '19 at 16:10

score 1 · Answer 2 · answered Mar 15 '19 at 14:16

Hi i think this solution can be work :

CREATE TABLE #TMP (
    string varchar(50)
)
GO

INSERT INTO #TMP
VALUES
     ('I have $4,000')
    ,('$44,450is what I have')
    ,('this $600 is what I have now')
    ,('$5 cause I am broke')
    ,('I have $10,000,000,000')
    ,('No Money Here')
GO

SELECT 
    *,
    PATINDEX('%[^0-9,]%',SUBSTRING(string,CHARINDEX('$',string)+1, LEN(string))),
    SUBSTRING(
        string, 
        CHARINDEX('$',string), 
        CASE WHEN PATINDEX('%[^0-9,]%',SUBSTRING(string,CHARINDEX('$',string)+1, LEN(string))) = 0  THEN  LEN(string) -1 ELSE PATINDEX('%[^0-9,]%',SUBSTRING(string,CHARINDEX('$',string)+1, LEN(string))) END
    ) AS Result
FROM 
    #TMP

DROP TABLE 
  #TMP
GO

The way it's due too the PATINDEX function return 0 if the pattern was at the end of string.

But i didn't know what your find if you receive multiple amount in 1 string like : helo i have $20 now but yesterday i have $100

For more information : https://learn.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-2017

Other post : PATINDEX returning 0 on matching rexpresson

This worked. I could've been more clear about my constraints - There will only be one dollar amount in these lines. Thanks! — Richard Wolff, Mar 15 '19 at 14:20

SQL Server: extracting $#,### from a string

2 Answers2