Netsuite Technical Consultant

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.

Puzzled how you got from *'Regex to extract a number including decimal places from a string'* to *'Netsuite Technical Consultant'*. — William Robertson, Mar 31 '22 at 21:24
What if the string is *''the total amount is 12.30 rupees and 1.23 vat'*? — William Robertson, Mar 31 '22 at 21:25

score 0 · Answer 1 · answered Mar 31 '22 at 12:39

0

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;

answered Mar 31 '22 at 12:39

EJ Egyed

5,791
1
8
23

score 0 · Accepted Answer · answered Mar 31 '22 at 12:43

0

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

oracle regular expression to extract decimal numbers from string

answered Mar 31 '22 at 12:43

Dietz

18
3

Netsuite Technical Consultant

2 Answers2