Convert Varchar2 into Number

Oracle SQL: Convert Varchar2 to Number and handle Nulls

The conversion may fail at the point. 0.33 is not a valid number in all languages.

You can explicitly specify the point as decimal separator:

to_number(cuser1, '99999.999')

And maybe you'd even have to trim blanks:

to_number(trim(cuser1), '99999.999')

But still there may be values that violate the pattern. Then a WHERE clause might help to only convert valid numbers. E.g.:

where regexp_like(cuser1, '^[[:digit:]]*\.{0,1}[[:digit:]]*$') or cuser1 is null

How to convert a Varchar2 value into a number in Oracle SQL?

You can find the SUBSTRing up until the first space character and then convert it using TO_NUMBER:

SELECT TO_NUMBER( SUBSTR( duration, 1, INSTR( duration, ' ' ) -1 ) )
FROM   details;

or, you can use a regular expression to get the substring of just the leading digits:

SELECT TO_NUMBER( REGEXP_SUBSTR( duration, '^\d+' ) )
FROM   details;

However, the best solution would be to stop using a VARCHAR2 data type and, instead, use a NUMBER column then you do not need to do the conversion and are storing numeric data in an appropriate data type rather than as a string. If you want to display the value with the units then perform that formatting when you need it.

SQL convert varchar2 to number

It does sound like you are using an Oracle database (varchar2 date type...)

If so, it looks like your session has an unexpected value for the NLS_NUMERIC_CHARACTERS NLS parameter.

You can check what you currently have by querying

select value
from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS';

On my database, it returns a value of .,, where that first character . represents what Oracle expects as the decimal character (The 2nd character, in this case the ,, is used to determine the group separator character). If your database returns a different set of 2 characters, then that would explain why it's unable to parse your values as numbers.

A couple of options:

Consider changing the NLS_NUMERIC_CHARACTERS NLS parameter for your session:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'; -- set '.' as the decimal character for this session.

After doing that, you should have no problem calling TO_NUMBER the way you tried already.

Alternatively, you can add additional parameters to the TO_NUMBER function call to force the correct decimal character for that call. Something like this:

SELECT to_number(BMI, '99D99', 'NLS_NUMERIC_CHARACTERS = ''.,''') FROM X;

Relevant documentation references

• TO_NUMBER Function
• Number Format Models
• NLS_NUMERIC_CHARACTERS

Convert VARCHAR to NUMBER with decimal on Oracle

I found the problem. I need to pass a mask as parameter to the to_number function. Like '999.999999999999999'

So:

select to_number('90.79493','999.999999999999999') from dual;
select to_number('90.146610399175472','999.999900000000000') from dual;
select to_number('90.34234324','999.999999999999999') from dual;

works for different size of numbers.

How to convert varchar2 to a number in SQL

You can include the dollar sign in the format:

select to_number('$12.00', '$999.99')

SQL Oracle - problem with converting varchar2 to number

You can start by only extracting numbers:

select regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 1),
       regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 2)
from (select 'POINT(-122.387539744377 37.7604575554348)' as str from dual) x

The results should work with to_number():

select to_number(regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 1)),
       to_number(regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 2))
from (select 'POINT(-122.387539744377 37.7604575554348)' as str from dual) x;

Oracle now supports error handling, so you can include that as well:

select to_number(regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 1) default null on conversion error),
       to_number(regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 2) default null on conversion error)
from (select 'POINT(-122.387539744377 37.7604575554348)' as str from dual) x

how to convert varchar2 to number in oracle?

Try this

(not tested as I don't have an oracle connection at the moment)

  CREATE OR REPLACE TRIGGER trg_ta_totals
  BEFORE INSERT OR UPDATE ON T_A
  FOR EACH ROW

    DECLARE
        vnum number;
    BEGIN

    IF instr(:NEW.total,'p') > 0 THEN
       vnum:=TO_NUMBER(SUBSTR(:NEW.total,0,LENGTH(:NEW.total)-1));     
    ELSIF instr(:NEW.total,'N') > 0 THEN
       vnum:=TO_NUMBER(SUBSTR(:NEW.total,0,LENGTH(:NEW.total)-1))*-1;
    END IF; 

    :NEW.total_num := vnum; 

    END;

How do I convert VARCHAR Column into NUMBER?

If your database supports to_number() the syntax is:

select di.Donor_Name DI, 
       acz.Address_City ACZ, 
       dd.Donation_Type as DD, 
       to_number(dd.Donation) 
from DI di join
     ACZ acz
     on ? join  -- MISSING JOIN CONDITION
     DD dd 
     on ?       -- MISSING JOIN CONDITION
where dd.Donation_Type = 'Currency' AND dd.Donation > 499 
order by Donor_Name;

You left out all your join conditions.

In most databases, you would solve the problem by storing the value using the correct datatype. You can also cast() the value:

cast(dd.donation as numeric(15, 4))  -- or whatever

---

Related Topics