Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help: to_number SQL*Plus error
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))
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