This discussion is archived
13 Replies Latest reply: Oct 25, 2012 7:11 AM by Frank Kulash RSS

Verify the number and char column

899280 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    use regular expression
  • 2. Re: Verify the number and char column
    Sven W. Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Dup post removed..

    Edited by: jeneesh on Oct 12, 2012 4:10 PM
  • 11. Re: Verify the number and char column
    Frank Kulash Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    ;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points