1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Can we get only Number from varchar column through SQL?

Discussion in 'SQL PL/SQL' started by jagadekara, Mar 13, 2015.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I have a table like below.

    CREATE TABLE TEST21(COL_NAME VARCHAR2(30));

    INSERT INTO TEST21 VALUES ('23');
    INSERT INTO TEST21 VALUES ('32569');
    INSERT INTO TEST21 VALUES ('JAGAN');
    INSERT INTO TEST21 VALUES ('12JAGAN2');

    Now I NEED DATA which contains only number.

    SELECT * FROM TEST21 where .....(PROPOSED CONDITION);

    COL_NAME
    -----------
    23
    32569


    So Is it possible?
     
  2. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Code (SQL):

    ...
    WHERE regexp_like(col_name, '^[[:digit:]]+$')  
     
     
    jagadekara likes this.
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks eras.

    I got one more answer from friends like below.

    select * from test21
    where regexp_instr(col_name,'[[:alpha:]]') = 0
     
  4. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello Jagadekara,

    You can use below query also

    SELECT * FROM TEST21 where ISNUMERIC(col_name) = 1
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Hegde,

    But it shows ISNUMERIC: Invalid Identifier
     
  6. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hi Jagadekara,

    It is displaying output to me .

    I am using below version.

    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Is it?

    I am also having below version.
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
     
  8. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    There are no built-in function called ISNUMERIC in any version of Oracle database. I guess it's user-defined (maybe particular application) function.
    Can You show output ?:
    Code (SQL):

    SELECT owner, object_type, object_name FROM all_objects WHERE object_name = 'ISNUMERIC'
     
     
    RG Hegde likes this.
  9. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    jagadekara,

    using "regexp_instr(col_name,'[[:alpha:]]') = 0" in Your case it's not the same as "regexp_like(col_name, '^[[:digit:]]+$')"

    Let's look:
    Code (SQL):

    WITH qry AS (SELECT '2563' col_name FROM dual
                     UNION ALL
                     SELECT '56 87' col_name FROM dual
                     UNION ALL
                     SELECT ',7.96' col_name FROM dual
                     UNION ALL
                    SELECT '*84;7' col_name FROM dual)
    SELECT *
    FROM tqry
    WHERE regexp_instr(num,'[[:alpha:]]') = 0
     
     
  10. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    oops...!!!

    It is a custom Function. The logic is
    Code (SQL):

    CREATE OR REPLACE FUNCTION APPS.isnumeric(v IN varchar2) RETURN NUMBER AS
       -- returns 1  if the parameter is numeric
       -- 0 non numeric
       num   NUMBER;
    BEGIN
       num := to_number(v);
       RETURN 1;
    exception
       WHEN others THEN
          RETURN 0;
    END;
    /