Re: SQL Function

On Mon, 29 Apr 2002 16:13:48 GMT, Daniel Morgan <dmorgan_at_exesolutions.com> wrote:

>Prasad Yarlagadda wrote:
>
>> Is there any standard SQL function available to check whether an array of
>> characters is a String or a Number? I am looking for something like
>> IS_NUMBER or IS_CHARACTER. I don't want to write a function for it if oracle
>> has already provided.
>>
>> TIA,
>> Prasad
>
>No. This must be done in a PL/SQL block such as the following:
>
>BEGIN
> test := TO_NUMBER(somevalue);
> <do something>
>EXCEPTION
> WHEN OTHERS THEN
> <do something else>
>END;
>
>If you don't do it this way you need to be very careful about what you decide is
>a number. For example ... is 8.1.7.3 a number? It would be with a lot of code
>I've seen written.
>
>Daniel Morgan

An alternative solution would be replace all numeric characters by 'X' and check whether the string contains all X-es now REPLACE(<affected_column>,'0123456789','XXXXXXXXXX') = RPAD('',length(<affected_column>,'X')

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Apr 29 2002 - 11:27:53 CDT