Converting varchar to number

Question

I recently did a TO_NUMBER(column_name) and it converted it to a number successfully. My question is in regards to the data type, it is data type NUMBER in Oracle, originally the column is a varchar. Is it possible to do a TO_NUMBER conversion that would result in a data type of NUMBER(19,0) rather than just a NUMBER data type?

Here is my statement:

select column1, cast(regexp_replace(column1, 'hi') as NUMBER(19,0)) TEST from table1

However when I try to create a view with this I get:

Error(s) parsing SQL: Unexpected token at 63 near ,0)) TEST from table1.
Unexpected token at 65 near )) TEST from table1.

Is cast not possible to create a view?. Thanks,

Husqvik · Accepted Answer · 2015-10-26 02:52:27Z

1

Use CAST function: CAST(TO_NUMBER(column_name) AS NUMBER(19, 0))

answered Oct 26, 2015 at 2:52

Husqvik

5,8191 gold badge23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RobertC Over a year ago

Hello I have edited my post, unable to create a view with cast...just a select statement it works but not when I try to create a view out of it.

Husqvik Over a year ago

I have no problem to create view with the same expression.

RobertC Over a year ago

Ah it must be a sqldeveloper bug...i ran it from sqlplus* and it worked like a charm thanks for all the help!

Collectives™ on Stack Overflow

Converting varchar to number

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related