CODEWITHSUNDEEP
javasqloracle-database
Bia
Bia

Reputation: 183

Oracle: generate a numeric sequence whose values start with a certain digit

I need to define a numeric sequence for a column of my table in Oracle. The only contraint I've been given requires all values to start with 9. So, the list of autogenerated values should start like the following:

9, 90, 91, 92...99, 900, 901...999, 9000, 9001...9999, 90000,...

Any idea about how to solve this problem via SQL (by creating opportunely and editing by sequence) and/or by Java code? In some posts I've read on web, people suggest to modify sequence this way:

ALTER SEQUENCE gokhan.sample_seq INCREMENT BY -500;
SELECT gokhan.sample_seq.NEXTVAL FROM dual;
ALTER SEQUENCE gokhan.sample_seq INCREMENT BY 1;

This requires the computation of new increment every time. If possible, I'd like to write a portion of SQL code that automates such process.

Thanks for support.

Upvotes: 0

Views: 393

Answers (2)

Tomáš Záluský
Tomáš Záluský

Reputation: 12030

Use normal sequence 0,1,2,3,... and for any x retrieved from this sequence compute the mapped element of your fancy sequence directly.

When initial value of sequence is 0, then elements 1-10 map to 90-99, elements 11-110 to 900-999, elements 111-1110 to 9000-9999 etc. Hence for each x of input sequence (for example 500) find the lower bound of the interval (111), compute peer of lower bound (900) and compute offset of peer (9389).

Code:

with sequence (x) as (
  select level - 1 from dual connect by level < 1001
), o (x,ones) as (
  select x, case x when 0 then 0 else to_number(rpad('1',to_char(1 + trunc(log(10,x))),'1')) end as ones
  from sequence
), m (x,ones,min) as (
  select x, ones, case when x >= ones then ones else (ones - 1) / 10 end as min 
  from o
)
select x, case x when 0 then 9 else x - min + 9 * power(10, 1 + trunc(log(10,min))) end as result from m;

Translating to PLSQL is up to you. Note the proposal of concatenating 9 to digits of normal sequence doesn't produce required result since such sequence would grow 10/9 times quicker (number of digits would increase by 1 after 9,90,900,... elements whereas in your sequence the number of digits increases by 1 after 10,100,1000,... elements). It would be lots simpler though. Despite you didn't describe reasons for such a logic, I would consider replacing by simpler logic anyway.

Upvotes: 0

Eritrean
Eritrean

Reputation: 16498

The only difficulty I see if you want to implement it in java is to calculate the next value for a given number. But luckily your borders are the powers of ten (10, 100, 1000 ...) otherwise you increment by one. Something like the following should give you an initial approach

public class Test {

//A method to check if a given number is a power of ten :

    public static boolean powerOf10(int n) {
        int max_power10 = 1_000_000_000;
        if (n > max_power10 ) return false;
        int i = 1;
        while (i < n) i *= 10;   
        return i == n;
    }

 //Then you can calculate the next value by either adding one or 
 //adding one and multipling by 9 if last value plus one is a power of ten

    public static int nextVal(int lastVal){        
        if(!powerOf10(lastVal + 1)){
            return lastVal + 1;
        }
        return 9 * (lastVal + 1);
    }

//Note: for the sake of readability i omitted the check if the last value is a valid value (a number starting with 9)

//Demo 
    public static void main(String[] args){
        int MIN_VALUE = 9;
        for(int i = MIN_VALUE; i < 10000; i = nextVal(i)){
            System.out.println(i);
        }
    }
}

Upvotes: 1

Related Questions