how to get an exact number from a string in SQL. I have a string like 'the total amounts is 12.30 rupees and vat' in this string i want to extract 12.3 for calculation purposes in SQL, NetSuite formula field

I have tried replace function to get all numerics from a string but it getting 1230 instead of 12.30 so it is not consider a dot.

CodePudding user response：

Using a simple regex expression, you can get the numeric value

select regexp_substr('the total amounts is 12.30 rupees and vat', '\d \.?\d ?') from dual;

CodePudding user response：

SELECT REGEXP_SUBSTR('Test 12.30 test', '-?\d (\.\d )?')
FROM dual;

oracle regular expression to extract decimal numbers from string