11 Replies Latest reply: Aug 21, 2007 6:46 PM by 594119 RSS

    isnumeric

    245466
      Is there a function in ORACLE like the isnumeric in SQL Server?

      This is what I would like to do: I have a table tbl that has one column col varchar2(5) I would like to append 0 to the begining of the column if the field is numeric.

      In SQL Server, This is how I will do it

      SELECT * FROM TBL

      COL
      -----
      1
      11
      1123
      ABC
      ZZ
      18AB

      SELECT CASE ISNUMERIC(COL)
                WHEN 1 THEN RIGHT('00000'+LTRIM(RTRIM(COL)),5)
           ELSE COL

           END AS COL
      FROM TBL

      COL
      -----
      00001
      00011
      01123
      ABC
      ZZ
      18AB

      Thanks

        • 1. re:isnumeric
          23650
          select col, decode(replace(translate(col, '1234567890.', '00000000000'), '0', null), null, lpad(col,
           5, '0'), col) col 
            from tbl;
          
          COL   COLPA
          ----- -----
          1     00001
          11    00011
          1123  01123
          ABC   ABC
          ZZ    ZZ
          18AB  18AB
          12345 12345
          • 2. re:isnumeric
            190681
            Look This:

            Tbl (
            coll varchar2(05))


            begin
            declare
            cursor c1 is
            select coll
            from tbl;
            value_n number(10);

            begin
            for r1 in c1 loop
            begin
            value_n := to_number(r1.coll);

            update tbl
            set coll = lpad(r1.coll,5,'0')
            where coll = r1.coll;
            exception
            when others then
            null;
            end;
            end loop;
            end;
            commit;
            end;

            Good Luck
            • 3. re:isnumeric
              lewisc
              CREATE OR REPLACE FUNCTION isnumber( p_in_data IN VARCHAR2 ) RETURN BOOLEAN
              v_temp NUMBER;
              BEGIN
              v_temp := TO_NUMBER( p_in_data );

              RETURN TRUE;

              EXCEPTION
              WHEN OTHERS THEN
              RETURN FALSE;
              END;
              /


              • 4. re:isnumeric
                lewisc
                I forgot to put the select in my reply above.

                SELECT decode(ISNUMERIC(COL), TRUE, RIGHT('00000'+LTRIM(RTRIM(COL)),5), COL ) AS COL
                FROM TBL

                Lewis


                • 5. re:isnumeric
                  lewisc
                  I also spelled my function isnumber not isnumeric. It's been a long day.
                  I forgot to put the select in my reply above.
                  SELECT decode(ISNUMERIC(COL), TRUE, RIGHT('00000'+LTRIM(RTRIM(COL)),5), COL ) AS COL
                  FROM TBL
                  Lewis
                  • 6. re:isnumeric
                    12826
                    Not to say the other solution will work this may be simpler to implement
                    Using you example table

                    select
                    decode(decode(UPPER(col), LOWER(col), 1, 0),
                    1, Lpad(col,5,'0'), col) col
                    from test_num

                    RESULTS
                    COL
                    --------
                    00001
                    00011
                    01123
                    ABC
                    ZZ
                    18AB


                    Is there a function in ORACLE like the isnumeric in SQL Server?
                    This is what I would like to do: I have a table tbl that has one column col varchar2(5) I would like to append 0 to the begining of the column if the field is numeric.
                    In SQL Server, This is how I will do it
                    SELECT * FROM TBL
                    COL
                    -----
                    1
                    11
                    1123
                    ABC
                    ZZ
                    18AB
                    SELECT CASE ISNUMERIC(COL)
                              WHEN 1 THEN RIGHT('00000'+LTRIM(RTRIM(COL)),5)
                         ELSE COL
                         END AS COL
                    FROM TBL
                    COL
                    -----
                    00001
                    00011
                    01123
                    ABC
                    ZZ
                    18AB
                    Thanks
                    • 7. re:isnumeric
                      12826
                      Not to say the other solution will not work this may be simpler to implement
                      Using you example table

                      select
                      decode(decode(UPPER(col), LOWER(col), 1, 0),
                      1, Lpad(col,5,'0'), col) col
                      from test_num

                      RESULTS
                      COL
                      --------
                      00001
                      00011
                      01123
                      ABC
                      ZZ
                      18AB


                      Is there a function in ORACLE like the isnumeric in SQL Server?
                      This is what I would like to do: I have a table tbl that has one column col varchar2(5) I would like to append 0 to the begining of the column if the field is numeric.
                      In SQL Server, This is how I will do it
                      SELECT * FROM TBL
                      COL
                      -----
                      1
                      11
                      1123
                      ABC
                      ZZ
                      18AB
                      SELECT CASE ISNUMERIC(COL)
                                WHEN 1 THEN RIGHT('00000'+LTRIM(RTRIM(COL)),5)
                           ELSE COL
                           END AS COL
                      FROM TBL
                      COL
                      -----
                      00001
                      00011
                      01123
                      ABC
                      ZZ
                      18AB
                      Thanks
                      • 8. re:isnumeric
                        1122
                        Here's another way.
                        Similar to one of Lewis Cunningham's posts above.
                        This function will return a 1 if the string is a number, and a 0 if it is not.
                        CREATE OR REPLACE FUNCTION is_number(string_in VARCHAR2)
                        RETURN NUMBER
                        AS
                        
                        BEGIN
                             RETURN SIGN(ABS(TO_NUMBER(string_in))+1);
                          EXCEPTION
                          WHEN VALUE_ERROR
                          THEN RETURN 0;
                        END;
                        /
                        
                        
                        SELECT
                             IS_NUMBER(12345) is_num
                        FROM
                             dual
                              IS_NUM 
                        ------------ 
                                   1 
                        
                        SELECT
                             IS_NUMBER(-123.66) is_num
                        FROM
                             dual
                              IS_NUM 
                        ------------ 
                                   1 
                                   
                        SELECT
                             IS_NUMBER('XXX123') is_num
                        FROM
                             dual
                              IS_NUM 
                        ------------ 
                                   0 
                        
                        SELECT
                             IS_NUMBER('@#$%^') is_num
                        FROM
                             dual
                              IS_NUM 
                        ------------ 
                                   0 
                        • 9. re:isnumeric
                          233984
                          Todd's approach is the BEST in this regard.
                          Thanks,
                          Sri DHAR
                          • 10. re:isnumeric
                            Barbara Boehmer
                            http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2460648511591
                            • 11. Re: re:isnumeric
                              594119
                              Thanks.

                              This worked!!