How to group serial numbers in oracle?

Question

I have a comma separated string.

For example 2000,2001,2002,2005,2006,2007 and 2010 .

I want to group consecutive numbers.

My conclusion should be 2000-2003,2005-2007 and 2010 . Is there a way to do this in an Oracle Stored Procedure?

Answer 1

 18:42:15 SYSTEM@dwal > l 1 with p as ( 2 select replace('2000,2001,2002,2005,2006,2007 and 2010',' and ',',') s, '[0-9]{4}' r from dual 3 ), ex as ( 4 select regexp_substr(s,r, 1, level) as y 5 from p 6 connect by level <= regexp_count(s, r) 7 ), grp as ( 8 select connect_by_root(y) s, y 9 from ( select e1.yy, e2.yp from ex e1, ex e2 where e1.y - 1 = e2.y(+) ) 10 connect by prior y = p 11 start with p is null 12 ), agg as ( 13 select listagg(s||decode(max(y), s, null, '-'||max(y)), ',') within group (order by s) str 14 from grp group by s 15 ) 16* select regexp_replace(str, ',', ' and ', 1, regexp_count(str, ',')) result from agg 18:42:16 SYSTEM@dwal > / RESULT ------------------------------ 2000-2002,2005-2007 and 2010 Elapsed: 00:00:00.02 

Answer 2

Disclaimer - I do not recommend using this solution "as is", but it can provide ideas and it was fun to write it

I assume you have a column with csv rows in the table.

If you are using oracle 11gR2, you can use recursive CTEs

Here is the sqlfiddle daemon

 with t as ( select replace(replace(v, ' and ', ','), ' ','') v from strings ), rcte(text, token, res) as ( select v, regexp_substr(v, '^\d*[^,]'), regexp_substr(v, '^\d*[^,]') ||'-' from t union all select regexp_replace(text, '^\d*,', ''), regexp_substr(text, '^\d*[^,]'), case when regexp_substr(text, '^\d*[^,]') = token then res when regexp_substr(text, '^\d*[^,]') = token+1 then regexp_replace(res, '-\d*$', '-'||(token+1)) else rtrim(res, '-') || ',' || regexp_substr(text, '^\d*[^,]') || '-' end from rcte where text <> token ) select rtrim(res, '-') from rcte where text = regexp_substr(rtrim(res, '-'), '\d*$'); 

(This can be done without regular expressions)

Answer 3

This can be done using SQL using analytic functions.

* Update 1: * Answer updated using the parser function, skipped in the previous version.

* Update 2: * Added final string composition

 with p as ( -- Parameter string select replace('2000,2001,2002,2005,2006,2007 and 2010',' and ',',') s from dual ), ex as ( -- Parse string to sequence select to_number( substr( s, decode( level, 1, 1, instr(s,',',1,level-1)+1 ), decode( instr(s,',',1,level), 0, length(s)+1, instr(s,',',1,level) ) - decode( level, 1, 1, instr(s,',',1,level-1)+1 ) ) ) as y from p connect by instr(s,',',1,level-1) > 0 ), period_set as ( select -- Make final string for each interval start y, lag(y) over (order by y) prior_y, max(y) over (partition by 1) max_y, y || (case when is_end > 1 then null else '-' ||end_y end) as interval_string from ( -- For each start find interval end select y, is_start, is_end, lead(y) over (order by y) end_y from ( -- Find if previous/next value differs more then by one. -- If so, mark as start/end select y, nvl(y - prev_y, 100) is_start, nvl(next_y - y, 100) is_end from ( -- Find previous/next value in sequence select y, lag(y) over (order by y) prev_y, lead(y) over (order by y) next_y from ex ) ) where is_start > 1 or is_end > 1 ) where is_start > 1 ) select replace( substr( sys_connect_by_path( decode(y,max_y,'m', null) || interval_string, ',' ),2 ), ',m', ' and ' ) result_str from period_set where connect_by_isleaf = 1 start with prior_y is null connect by prior y = prior_y 

SQL Fiddle can be found here .

Answer 4

Assuming your data will be separated by commas and entered, you can mention any years separated by a comma.

 DECLARE v_str VARCHAR(100) := '&n'; v_instr NUMBER; v_instr1 NUMBER; v_g VARCHAR(50); v_F VARCHAR(50); v_OUT VARCHAR(50); v_OUT1 VARCHAR(50); v_TEMP VARCHAR(50); v_TMP VARCHAR(50) := ' '; v_cnt NUMBER :=0; V_FLAG NUMBER :=1; BEGIN FOR i IN 1..Length(v_str)-Length(REPLACE(v_str,',',''))+1 LOOP IF i = 1 THEN v_g := SubStr(v_str,1,InStr(v_str,',',1,i)-1); V_F := V_G; ELSE v_instr := InStr(v_str,',',1,i-1); v_instr1 := InStr(v_str,',',1,i); IF(v_cnt+1 <= Length(v_str)-Length(REPLACE(v_str,',',''))) then v_g := SubStr(v_str,v_instr+1,v_instr1-v_instr-1); IF V_FLAG = 0 THEN V_F := V_G; V_FLAG :=1; END IF; ELSE v_g := SubStr(v_str,v_instr+1); IF V_FLAG = 0 THEN V_F := V_G; V_FLAG :=1; END IF; END IF; END IF; v_cnt := v_cnt+1; --IF(I>1) THEN IF(V_TEMP+1 = V_G) THEN IF(V_OUT IS not NULL) THEN V_OUT := V_OUT||'-'||V_G; ELSE V_OUT := V_F||'-'||V_G; END IF; ELSE V_OUT1 := SubStr(V_OUT,1,5)||SubStr(V_OUT,-4); V_OUT := NULL; v_out := v_g; V_FLAG := 0; END IF; --END IF; V_TEMP := To_Number(V_G); --Dbms_Output.put_line(v_g); IF(v_tmp <> v_out1) THEN SELECT Decode(instr(v_OUT1,'-'),0,subStr(V_OUT1,1,4),v_out1) INTO v_out1 FROM dual; Dbms_Output.put_line('Year span : '||v_OUT1); v_tmp := v_out1; END IF; END LOOP; SELECT Decode(Length(v_OUT),4,v_out,subStr(V_OUT,1,5)||SubStr(V_OUT,-4)) INTO v_out1 FROM dual; Dbms_Output.put_line('Year span : '||v_out1); END; 

Answer 5

use math

 select min(seq) as range_from , max(seq) range_to , count(*) as cnt from sometable group by ceil(seq/3) * 3 

this part of ceil(seq/3) * 3 rounds the number to the nearest multiple of three. if you need a range of 5 just use ceil(seq/5) * 5 . Greetings!

Answer 6

Great QUESTION! Please check my logic ...

 with test as ( select '2000,2002,2003,2004,2006,2007' str from dual ) ,test1 as ( select split1, lead(split1, 1, null) over (order by split1 asc) lead_no, level1 from ( select to_number(regexp_substr (str, '[^,]+', 1, rownum)) split1, level as level1 from test b connect by level <= length (regexp_replace (str, '[^,]+')) + 1 )x ) --select * from test1 ,test2 (split1, lead_no, level1, op, op1) as( select split1, lead_no, level1, (case when split1+1=lead_no then to_char(split1) else NULL end), (case when split1+1=lead_no then NULL else to_char(split1) end) from test1 where level1=1 union all select a.split1, a.lead_no, b.level1+1, (case when a.split1+1=a.lead_no and to_char(b.op) is not null then to_char(b.op) when a.split1+1=a.lead_no and to_char(b.op) is null then to_char(a.split1) else null end), (case when (a.split1+1<>a.lead_no and to_char(b.op)<>to_char(a.split1)) OR (a.lead_no is null and to_char(b.op) is not null) then to_char(b.op) ||'-'||to_char(a.split1) when a.lead_no is null then to_char(a.split1) else null end) from test1 a inner join test2 b on a.level1 = b.level1+1 ) select op1 from test2 where op1 is not null 

Answer 7

You should use cursor to scroll through these years and do something like this:

 CREATE OR REPLACE Function concatYears RETURN varchar; DECLARE oldYear number; concat varchar(300); newGroup boolean; cursor cur1 is SELECT year FROM dates ORDER BY year ASC; BEGIN oldYear:=0; newGroup:=true; concat := ''; FOR row in c1 IF oldYear == 0 THEN oldYear := row.year; END IF; IF newGroup == true THEN concat := concat || CAST(oldYear AS varchar(4)); newGroup:= false; ELSE IF row.year > oldYear+1 THEN concat:= concat || '-' || CAST(oldYear AS varchar(4)) || ' , '; newGroup:=true; END IF; END IF; oldYear:=row.year; END LOOP; RETURN concat; END;