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

You may want to use ISNUMERIC user-defined-function in Sample UDFs for Migration

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").

Ok this is good, I can't create any procedures on this database, but i can use this translate functionality from isNumeric.

Thanks