Increment special chapter on index (book index style - 1.2.3.2) by sql

Question

Hi

I have a column holding a varchar2 value in book index style - means something like 1.2.3.2.1

I need a to incement a special chapter for recursive select. I want to match 1.2.1 with 1.3.1 and 1.2.2 with 1.3.2 and so on. I am wondering if this can be done without making a pl/sql function. I have tried to do this by regexp but no success, any idea?

select to_char(value) as f from data_parsed
start with seq like '1.1.%'
connect regexp_replace(
   seq, '\.(\d+)\.', '.' || to_number('\1')+1 || '.') = prior seq
;

Answer 1

If the number is always in the same position, you can use substr/instr to get what you are looking for:

connect by
    prior seq = 
    substr(seq, 1, instr(seq, '.', 1, 1)) -- string up to and including first period 
    || (substr(seq, instr(seq, '.', 1, 1) + 1, instr(seq, '.', 1, 2) - instr(seq, '.', 1, 1) - 1) - 1) -- number between the first and second period minus 1
    || substr(seq, instr(seq, '.', 1, 2)) -- string after and including second period

I am sure there is a cleaner looking way to accomplish the same thing using regexp, but I am no regexp expert.