ORACLE SQL:Get all integers between two numbers

Admin 1
Viewed 78524

Is there any way to select the numbers (integers) that are included between two numbers with SQL in Oracle; I don't want to create PL/SQL procedure or function.

For example I need to get the numbers between 3 and 10. The result will be the values 3,4,5,6,7,8,9,10.

Thx.

numbers oracle sql
13 Answers

this single line query will help you,

select level lvl from dual where level<:upperbound and 

                              level >:lowerbound connect by level<:limt

For your case:

select level lvl from dual where level<10 and level >3 connect by level<11

let me know if any clarification.

user_18594770

I want to share an usefull query that converts a string of comma and '-' separated list of numbers into a the equivalent expanded list of numbers:

An example that converts '1,2,3,50-60' into 

1
2
3
50
51
...
60

select distinct * from (SELECT (LEVEL - 1) + mini as result FROM (select REGEXP_SUBSTR (value, '[^-]+', 1, 1)mini ,nvl(REGEXP_SUBSTR (value, '[^-]+', 1, 2),0) maxi from (select REGEXP_SUBSTR (value, '[^,]+', 1, level) as value from (select '1,2,3,50-60' value from dual) connect by level <= length(regexp_replace(value,'[^,]*'))+1)) CONNECT BY Level <= (maxi-mini+1)) order by 1 asc;

You may use it as a view and parametrize the '1,2,3,50-60' string

user_37675030 
create table numbers (value number);

declare
    x number;
begin
    for x in 7 .. 25
    loop
        insert into numbers values (x);
    end loop;
end;
/
user_96546150

In addition to the answers already provided, it is possible to combine the listagg function with connect by to obtain the result in the format mentioned in the question. See a code example below:

SELECT 
   DBMS_LOB.SUBSTR(LISTAGG(S.INTEGERS,',' ) WITHIN GROUP (ORDER BY S.INTEGERS), 300,1) RESULT 
FROM 
   (SELECT 
      INTEGERS 
   FROM 
      ( SELECT ROWNUM INTEGERS FROM DUAL CONNECT BY LEVEL <= 10) 
   WHERE 
      INTEGERS >= 3 
   ) S;

OutPut:

SQL> 
RESULT
----------------
3,4,5,6,7,8,9,10
user_58570230
Related