Netsuite Technical Consultant [closed]

Question

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.

Answer 1

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

oracle regular expression to extract decimal numbers from string

Answer 2

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;