Home » RDBMS Server » Server Administration » Convert varchar to number in sql
Convert varchar to number in sql [message #372123] Thu, 11 January 2001 10:52 Go to next message
Kelsi
Messages: 2
Registered: January 2000
Junior Member
Please tell me the function or how to convert a character variable to a number in my stored procedure.

Thanks.
Kelsi

Report message to a moderator

Re: Convert varchar to number in sql [message #372138 is a reply to message #372123] Fri, 12 January 2001 14:18 Go to previous message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
select to_number('4') from dual;

If you enter an invalid number if returns an error, so you can write your own function as follows:

CREATE OR REPLACE FUNCTION to_num (v_value IN VARCHAR2)
RETURN NUMBER
IS
v_retval NUMBER;
BEGIN
v_retval := TO_NUMBER (v_value);-- try to convert it!
RETURN v_retval;-- return it!
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;-- Whoops! cant convert, return null!
END to_num;
/

Report message to a moderator

Previous Topic: Rownum problem in Oracle 7
Next Topic: Searching between alphanumeric ranges
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 17 23:06:52 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.