Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ID Color Ranges (nvarchar2!)
- -- ----- -------------------------
- 1 Blue 1-9,23.5-25.1,27.11,99.14
- 2 Red 4
- 3 Green 4.44-5.3
- 4 Black 18-22,101
- SQL> select id
- 2 , color
- 3 , to_number(case when ranges like '%-%' then regexp_substr(ranges,'[^-]+',1,1) else ranges end) low_value
- 4 , to_number(case when ranges like '%-%' then regexp_substr(ranges,'[^-]+',1,2) else ranges end) high_value
- 5 from colors
- 6 model
- 7 return updated rows
- 8 partition by (id,color)
- 9 dimension by (0 i)
- 10 measures (ranges,nvl(length(regexp_replace(ranges,'[^,]')),0) + nvl2(ranges,1,0) as number_of_parts)
- 11 ( ranges[for i from 1 to number_of_parts[0] increment 1]
- 12 = regexp_substr(ranges[0],'[^,]+',1,cv(i))
- 13 )
- 14 /
- ID COLOR LOW_VALUE HIGH_VALUE
- -------------------- ----- -------------------- --------------------
- 2 Red 4 4
- 1 Blue 1 9
- 1 Blue 23.5 25.1
- 1 Blue 27.11 27.11
- 1 Blue 99.14 99.14
- 4 Black 18 22
- 4 Black 101 101
- 3 Green 4.44 5.3
- 8 rows selected.
- function value_included (p_value in number, p_ranges in varchar2)
- return number
- is
- l_ranges_tab apex_application_global.vc_arr2;
- l_values_tab apex_application_global.vc_arr2;
- l_retval number := 0;
- begin
- l_ranges_tab := apex_util.string_to_table (p_ranges, ',');
- for i in 1..l_ranges_tab.count loop
- l_values_tab := apex_util.string_to_table (l_ranges_tab(i), '-');
- if l_values_tab.count = 1 then
- if p_value = l_values_tab(1) then
- l_retval := 1;
- exit;
- end if;
- else
- if p_value between l_values_tab(1) and l_values_tab(2) then
- l_retval := 1;
- exit;
- end if;
- end if;
- end loop;
- return l_retval;
- end;
- select color from colors where value_included(5, ranges);
- function range_overlap (p_from in number, p_to in number, p_ranges in varchar2)
- return number
- is
- l_ranges_tab apex_application_global.vc_arr2;
- l_values_tab apex_application_global.vc_arr2;
- l_retval number := 0;
- begin
- l_ranges_tab := apex_util.string_to_table (p_ranges, ',');
- for i in 1..l_ranges_tab.count loop
- l_values_tab := apex_util.string_to_table (l_ranges_tab(i), '-');
- if l_values_tab.count = 1 then
- if l_values_tab(1) between p_from and p_to then
- l_retval := 1;
- exit;
- end if;
- else
- if p_to >= l_values_tab(1) and p_from <= l_values_tab(2) then
- l_retval := 1;
- exit;
- end if;
- end if;
- end loop;
- return l_retval;
- end;
- create or replace type range_type as object (range_from number, range_to number);
- create or replace type range_table as table of range_type;
- create or replace function range_to_nested_table(i_ranges in varchar2)
- return range_table pipelined is
- thisRange varchar2(4000);
- loop_counter number := 1;
- output_row range_type;
- begin
- loop
- thisRange := rtrim(regexp_substr(i_ranges, '[^,]*,?', 1, loop_counter), ',');
- exit when thisRange is null;
- loop_counter := loop_counter + 1;
- if thisRange like '%-%' then
- output_row := range_type(to_number(regexp_substr(thisRange, '[^-]*', 1, 1)),
- to_number(regexp_substr(thisRange, '[^-]*(-|$)', 1, 2)));
- else
- output_row := range_type(to_number(thisRange), to_number(thisRange));
- end if;
- pipe row(output_row);
- end loop;
- RETURN;
- end;
- with my_sample_data as (
- select 1 as id, 'Blue' as color, '1-9,23.5-25.1,27.11,99.14' as range from dual union all
- select 2 as id, 'Red' as color, '4' as range from dual union all
- select 3 as id, 'Green' as color, '4.44-5.3' as range from dual union all
- select 4 as id, 'Black' as color, '18-22,101' as range from dual
- )
- select id, color, range, b.*
- from my_sample_data a, table(range_to_nested_table(a.range)) b
- where 5 between b.range_from and b.range_to
Add Comment
Please, Sign In to add comment