Oracle - need to extract money like 59,432,000 $ between given strings

Question

I'm trying to do a regex match to return a substring. From this string:

TK 19120226477558So tien GD:+50,000,000

I would want to return the results: 50,000,000

From this string:

TK 22010000631740 tai +5,854 VND vao 23:59 25/01/16;
    so du TK tai 08:01 26/01/16 la: 5,336,331 VND.ND: Lai tien gui KKH

I would want to return the results: 5,854 and 5,336,331.

Thanks for any help!

Answer 1

The following SQL can get the result that has at least one comma ',':

SELECT REGEXP_SUBSTR('so du TK tai 08:01 26/01/16 la: 5,336,331 VND.ND: Lai tien gui KKH', '\d{1,3}(,\d{3})+') FROM DUAL;

But if the result you want to get is less than one thousand like '789', the pattern('\d{1,3}(,\d{3})+') is not correct.

It should need a more complex pattern. But your rule in the case is not very clear.