Re: Detecting Numeric Only Strings via SQL/Oracle

Hi, Oracler!

Chris Gatto <cgatto_at_nbnet.nb.ca> wrote in article <3295F505.120C_at_nbnet.nb.ca>...
>
> 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. There does not seem to be
> an Oracle

One of the solution is following.

   SELECT "DC_LINE_CODING"."AMOUNT_FL"
     FROM "DC_LINE_CODING"
    WHERE DECODE(

            INSTR(
              TRANSLATE(UPPER("DC_LINE_CODING"."GL_CODE_ID"),
                        "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ",
                        "0123456789XXXXXXXXXXXXXXXXXXXXXXXXXX"),
              "X"),
            0,"DC_LINE_CODING"."GL_CODE_ID",NULL) > 03999

UPPER & TRANSLATE put each alpha charecters into "X" on GL_CODE_ID. INSTR searches "X" on TRUNSLATE's result and returns position. If there is no "X", it returns 0.
And DECODE returns GL_CODE_ID on case of INSTR's result is 0, returns NULL on other cases (there are some "X").

BTW, it's puzzle.

-- 
Yasuhiro Ushitaki / Toyo Information Systems Co.,Ltd.