Re: Detecting Numeric Only Strings via SQL/Oracle

Chris Gatto wrote:  

> SELECT "DC_LINE_CODING"."AMOUNT_FL"
> FROM "DC_LINE_CODING"
> WHERE To_Number(SubStr("DC_LINE_CODING"."GL_CODE_ID",1,5)) > 03999
>
> The problem I have is with the WHERE clause. The GL Code Id is a 5
> character
> string which can contain alpha characters. I want to ignore these GL
> Code Ids
> and only consider numeric-only GL Code Ids.

You may try to use:

   SELECT "DC_LINE_CODING"."AMOUNT_FL"
     FROM "DC_LINE_CODING"
    WHERE SubStr("DC_LINE_CODING"."GL_CODE_ID",1,5) > '03999' and

           SubStr("DC_LINE_CODING"."GL_CODE_ID",1,5) <='999999'.

But this kind of method assumes that the digits 0~9 in your character set
are continuous. such as US-ASCII character set. You might look at your character set before using it.  

-- 
Hui, Kwok Miu (Xu, Guo Miao)         E-Mail: huikomix_at_dt.com.hk
Oracle DBA & Unix Administrator      Tel   : (00852)2589 3369
Cho Yang (Hong Kong) Co., Ltd.       Fax   : (00852)2559 3049