Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Unanswered: get First available number from column

    Hello

    I would like to get first number from column.

    In column i have numbers:

    1 2 3 4 6 8

    So the first free number is: 5
    Insert 5 to this column
    The second call should return: 7
    insert 7 to this column
    Another call should return: 9 [...]

    I know how to write this SQL, but the problem is that, this column is char type. So its possible to be like this:

    1 2 3 a b c 7 8 9

    This is my problem, I don't know how to filtered results.
    In SQL Server i can do something like that: Where isNumeric(number) = true
    But on DB2 i don't know any similar function. I tried also Like [0-9] but this doesn't work .

    My idea for SQL is something like this:

    SELECT distinct min(number+1) FROM table WHERE number+1 NOT IN (SELECT charColumn FROM table)

    But problem is get only numeric values.


    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to use ISNUMERIC user-defined-function in Sample UDFs for Migration

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH a_table(charcolumn) AS (
    VALUES
    '1' , '2' , '3' , 'a' , 'b' , 'c' , '7' , '8' , '9'
    )
    SELECT MIN( INT(charcolumn) ) + 1
     FROM  a_table a
     WHERE ISNUMERIC(charcolumn) = 1
       AND NOT EXISTS
           (SELECT 0
             FROM  (SELECT charcolumn
                     FROM  a_table
                     WHERE ISNUMERIC(charcolumn) = 1
                   ) b
             WHERE INT(b.charcolumn) = INT(a.charcolumn) + 1
           )
    ;
    ------------------------------------------------------------------------------
    
    1          
    -----------
              4
    
      1 record(s) selected.
    The reason I used subquery in NOT EXISTS subquery is to guaruntee the evaluation sequence of predicates
    ("ISNUMERIC(charcolumn) = 1" and "INT(b.charcolumn) = INT(a.charcolumn) + 1").
    Last edited by tonkuma; 01-17-11 at 10:28.

  3. #3
    Join Date
    Oct 2010
    Posts
    3
    Ok this is good, I can't create any procedures on this database, but i can use this translate functionality from isNumeric.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •