13 Replies Latest reply: Oct 25, 2012 9:11 AM by Frank Kulash RSS

    Verify the number and char column

    899280
      From a list of values from a number column, I need to verify is there any values not satisfying number(3).

      Ex- I have a column col1 where we have following values

      'qwq'
      '12.3'
      '1'
      '15'

      Can I write a query to check the value not satisfying number(3) datatype.Eg the query should return

      'qwq'
      '12.3'
      as the above two will not fit in to number(3) datatype columns
        • 1. Re: Verify the number and char column
          Rahul_India
          use regular expression
          • 2. Re: Verify the number and char column
            Sven W.
            with testdata as (select 'qwq' str from dual union all
                          select '12.3' str from dual union all
                          select '1' str from dual union all
                          select '15' str from dual)
            select str 
            from testdata
            where not regexp_like (str,'^\d{0,3}$');
            
            qwq
            12.3
            • 3. Re: Verify the number and char column
              Frank Kulash
              Hi,
              896277 wrote:
              From a list of values from a number column, I need to verify is there any values not satisfying number(3).

              Ex- I have a column col1 where we have following values

              'qwq'
              '12.3'
              '1'
              '15'
              If it's a NUMBER column, then it can't have values like 'qwq'. Did you mean a VARCHAR2 column?
              Can I write a query to check the value not satisfying number(3) datatype.Eg the query should return

              'qwq'
              '12.3'
              as the above two will not fit in to number(3) datatype columns
              12.3 will fit into a NUMBER (3) column. It will be rounded to 12.

              Perhaps you want to find strings that do not represent integers of 3 digits or less. If so, you can use something like this:
              SELECT     str
              FROM     table_x
              WHERE     NOT REGEXP_LIKE ( str
                                 , '^0*[0-9]{1,3}(\.0*)?$'
                             )
              ;
              Edited by: Frank Kulash on Oct 12, 2012 6:10 AM
              If you want to allow a leading + or - sign:
              SELECT     str
              FROM     table_x
              WHERE     NOT REGEXP_LIKE ( str
                                 , '^[+-]?0*[0-9]{1,3}(\.0*)?$'
                             )
              ;
              • 4. Re: Verify the number and char column
                jeneesh
                Like..
                SQL> with t as
                  2  (select 'qwq' str from dual union all
                  3  select '12.3' from dual union all
                  4  select '1' from dual union all
                  5  select '15' from dual union all
                  6  select '0000015' from dual union all
                  7  select '-15' from dual)
                  8  select str
                  9  from t
                 10  where not regexp_like(ltrim(str,'0'),'^[-|+]{0,1}[0-9]{1,3}$')
                 11   or str is null;
                
                STR
                -------
                qwq
                12.3
                • 5. Re: Verify the number and char column
                  jeneesh
                  But practically speaking, it is very difficult in REGEXP.

                  Because, +00000000152, you can insert into NUMBER(3)
                  SQL> create table n(c1 number(3));
                  
                  Table created.
                  
                  SQL> insert into n values('+0000123');
                  
                  1 row created.
                  You will have to use PL/SQL...
                  • 6. Re: Verify the number and char column
                    899280
                    Hi Jeneesh,

                    In

                    regexp_like(ltrim(str,'0'),'^[-|+]{0,1}[0-9]{1,3}$')

                    Can you please let me know what parameter is {0,1}?
                    • 7. Re: Verify the number and char column
                      jeneesh
                      896277 wrote:
                      Hi Jeneesh,

                      In

                      regexp_like(ltrim(str,'0'),'^[-|+]{0,1}[0-9]{1,3}$')

                      Can you please let me know what parameter is {0,1}?
                      It means, 0-1 occurances of the pattern(here 0 or 1 occurances of "-" or "+".)

                      {1,3} means 1 to 3 occurances

                      I would go for PL/SQL in your case
                      SQL> create or replace function is_valid(p1 varchar2) return varchar
                        2  deterministic is
                        3   ln_dummy number(3);
                        4   lc_out varchar2(3);
                        5  begin
                        6   begin
                        7    ln_dummy := to_number(p1);
                      --"Check whether the string will go to the table with its actual numeric value"
                      --"This can be removed, as per your requireemnt"
                        8    if ln_dummy = to_number(p1) then
                        9     lc_out := 'Yes';
                       10    else
                       11     lc_out := 'No';
                       12    end if;
                       13   exception
                       14    when others then
                       15     lc_out := 'No';
                       16   end;
                       17   return lc_out;
                       18  end;
                       19  /
                      
                      Function created.
                      
                      SQL> with t as
                        2  (select 'qwq' str from dual union all
                        3  select '12.3' from dual union all
                        4  select '1' from dual union all
                        5  select '15' from dual union all
                        6  select '0000015' from dual union all
                        7  select '+0000015' from dual union all
                        8  select '+0000015326' from dual union all
                        9  select '+150' from dual union all
                       10  select '-15' from dual)
                       11  select str
                       12  from t
                       13  where is_valid(str) = 'No';
                      
                      STR
                      -----------
                      qwq
                      12.3
                      +0000015326
                      Edited by: jeneesh on Oct 12, 2012 3:50 PM
                      • 8. Re: Verify the number and char column
                        Frank Kulash
                        896277 wrote:
                        Hi Jeneesh,

                        In

                        regexp_like(ltrim(str,'0'),'^[-|+]{0,1}[0-9]{1,3}$')

                        Can you please let me know what parameter is {0,1}?
                        {0,1} means the preceding expression can occur as few as 0, or as many as 1 times. ? means the same thing.
                        Remember that | has no special meaning inside square brackets, so
                        [-|+]
                        means any of the 3 characters, '-', '|' or '+'. I think Jeneesh meant
                        [-+]
                        This solution does not allow a decimal point in an integer value, like '2.', even though it fits, without rounding, into a NUMBER (3) column.

                        Edited by: Frank Kulash on Oct 12, 2012 6:30 AM
                        • 9. Re: Verify the number and char column
                          jeneesh
                          Frank Kulash wrote:
                          ? means the same thing.
                          I am aware of that, Frank. I thought the other representation is more explanatory.

                          Remember that | has no special meaning inside square brackets, so
                          Thanks for notifying that.. I was not aware of that - the fact that "|" has no meaning inside square backets...
                          I was using it in many places, without knowing this fact... :(

                          Edited by: jeneesh on Oct 12, 2012 4:09 PM
                          • 10. Re: Verify the number and char column
                            jeneesh
                            Dup post removed..

                            Edited by: jeneesh on Oct 12, 2012 4:10 PM
                            • 11. Re: Verify the number and char column
                              Frank Kulash
                              Hi,

                              My first reply did not consider the case of the NUMBER 0 being represented by '.0', '+.0' or '-.0', all of which fit losslessly into a NUMBER (3) column. This does:
                              SELECT     str
                              FROM     table_x
                              WHERE     NOT REGEXP_LIKE ( str
                                                 , '^[+-]?0*(([0-9]{1,3}(\.0*))|(\.0+))?$'
                                             )
                              ;
                              '.', however, is not allowed.
                              • 12. Re: Verify the number and char column
                                899280
                                with t as
                                (select 'qwq' str from dual union all
                                select '12.3' from dual union all
                                select '1' from dual union all
                                select '153' from dual union all
                                select '.0000015' from dual union all
                                select '-15' from dual)
                                select str
                                from t
                                where not regexp_like(ltrim(str,'0'),'^[-|+]{0,1}[0-9]{1,2}$')
                                or str is null;

                                In the above query what can I include to return '-' (negative) values also.
                                • 13. Re: Verify the number and char column
                                  Frank Kulash
                                  Hi,
                                  896277 wrote:
                                  In the above query what can I include to return '-' (negative) values also.
                                  In regular expressions:
                                  [+|-]
                                  means any character from the set of '+'. '|' or '-'. If you want to indicate a + sign only, and not the other 2 characters, then use
                                  \+
                                  instead. For example:
                                  SELECT     str
                                  FROM     t
                                  WHERE     NOT REGEXP_LIKE ( str
                                                     , '^\+?0*(([0-9]{1,3}(\.0*))|(\.0+))?$'
                                                 )
                                  OR      str     IS NULL
                                  ;