CODEWITHSUNDEEP
sqloracle-database
Omri
Omri

Reputation: 1646

Converting string to currency value

I'm using Oracle SQL. I have column with values of String type, that actually supposed to be a currency.

The strings values are in the following format:

103,2 %

I need to convert it to:

103.2

Aldo i need to convert the values type to Float.

Is there's a good conversation functions that can help me? Do i need to do that with Replace() function?

Upvotes: 0

Views: 1746

Answers (3)

Alex Poole
Alex Poole

Reputation: 191265

You can use replace to strip out the fixed % part, which changed 103,2 % to 103,2; and pass the result of that into to_number(). Since you seem to have a comma decimal separtator in the value and period in the databse you can specify the NLS_NUMERIC_CHARACTERS:

select to_number(replace('103,2 %', ' %'),
  '999999D99', 'NLS_NUMERIC_CHARACTERS='',.''') as value
from dual;

     VALUE
----------
     103.2

If you don't have quite a fixed format - the space may or not be there, for example, or you could have different currency symbols instead of % - then you could use translate as Gordon Linoff showed, or use a regular expression to remove any non-numeric characters:

select to_number(regexp_replace('103,2 %', '[^[:digit:],]'),
  '999999D99', 'NLS_NUMERIC_CHARACTERS='',.''') as value
from dual;

Upvotes: 1

gayan_ms
gayan_ms

Reputation: 153

You can convert character strings to numeric values by using the CAST(string AS DECIMAL) or CAST(string AS SIGNED INTEGER)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

How about something like this?

select cast(translate(col, ', %', '.') as float)

Upvotes: 2

Related Questions