Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help: to_number SQL*Plus error

Re: Help: to_number SQL*Plus error

From: Joost Ouwerkerk <joost.ouwerkerk_at_sickkids.on.ca>
Date: Tue, 22 Dec 1998 15:48:12 GMT
Message-ID: <367fbd44.91888393@resunix.sickkids.on.ca>


RTRIM and LTRIM only trim things off the left and right side of strings. A string like '1231FDA44BVF34' would not be stripped using RTRIM and LTRIM.

TRANSLATE might be my solution, however. The following would return a zero value rather than an error for strings with non-numeric characters:

SELECT TO_NUMBER(DECODE

                  (TRANSLATE(field,'0123456789~','~'),
                   null,field,
                   0))

FROM testable
;

On 22 Dec 1998 11:29:59 GMT, "Alexander I. Doroshko" <aid_at_grant.kharkov.ua> wrote:

>Try to use
>
> rtrim(ALREADY_TRANSLATED_COLUMN, '0123456789') is not null
>
>for searching of records with non-numeric characters.
>---
> Alexander I.Doroshko, aid_at_grant.kharkov.ua
> * Bank "Grant", Kharkov, Ukraine
>
>Joost Ouwerkerk <joost.ouwerkerk_at_sickkids.on.ca> wrote in article
><367e8cf4.13972395_at_resunix.sickkids.on.ca>...
>> I am trying to cast a VARCHAR2 field into a number using TO_NUMBER for
>> a calculation (credit card check digit verification algorithm) using
>> only SQL*plus. The script errors out with an 'Invalid number'
>> message, presumably because one of the VARCHAR2 field contains a
>> non-numeric character. I've already TRANSLATEd dashes and spaces out,
>> and I suppose I could add the whole alphabet in there, but is there no
>> other way to find out if a field contains non-numeric chars?
>
Received on Tue Dec 22 1998 - 09:48:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US