CODEWITHSUNDEEP
sqloracle-databasenumbers
George
George

Reputation:

ORACLE SQL:Get all integers between two numbers

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.

Upvotes: 24

Views: 85567

Answers (13)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

One way to generate numbers from range is to use XMLTABLE('start to end'):

SELECT TO_NUMBER(column_value)  integer_value
FROM XMLTABLE('3 to 10');

I added TO_NUMBER because COLUMN_VALUE is a string

DBFiddle Demo

Upvotes: 4

Genivan
Genivan

Reputation: 191

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

Upvotes: 0

diaphol
diaphol

Reputation: 127

create table numbers (value number);

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

Upvotes: 0

antoniolvsa
antoniolvsa

Reputation: 1

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

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881463

The first thing I do when I create a new database is to create and populate some basic tables.

One is a list of all integers between -N and N, another is a list of dates 5 years in the past through 10 years in the future (a scheduled job can continue creating these as needed, going forward) and the last is a list of all hours throughout the day. For example, the inetgers:

create table numbers (n integer primary key);
insert into numbers values (0);
insert into numbers select n+1 from numbers; commit;
insert into numbers select n+2 from numbers; commit;
insert into numbers select n+4 from numbers; commit;
insert into numbers select n+8 from numbers; commit;
insert into numbers select n+16 from numbers; commit;
insert into numbers select n+32 from numbers; commit;
insert into numbers select n+64 from numbers; commit;
insert into numbers select n+128 from numbers; commit;
insert into numbers select n+256 from numbers; commit;
insert into numbers select n+512 from numbers; commit;
insert into numbers select n+1024 from numbers; commit;
insert into numbers select n+2048 from numbers; commit;
insert into numbers select n+4096 from numbers; commit;
insert into numbers select n+8192 from numbers; commit;
insert into numbers select -n from numbers where n > 0; commit;

This is for DB2/z which has automatic transaction start which is why it seems to have naked commits.

Yes, it takes up a (minimal) space but it makes queries much easier to write, simply by selecting values from those tables. It's also very portable across pretty much any SQL-based DBMS.

Your particular query would then be a simple:

select n from numbers where n >=3 and n <= 10;

The hour figures and date ranges are quite useful for the sort of reporting applications we work on. It allows us to create zero entries for those hours of the day (or dates) which don't have any real data so that, instead of (where there's no data on the second of the month):

Date       | Quantity
-----------+---------
2009-01-01 |        7
2009-01-03 |       27
2009-01-04 |        6

we can instead get:

Date       | Quantity
-----------+---------
2009-01-01 |        7
2009-01-02 |        0
2009-01-03 |       27
2009-01-04 |        6

Upvotes: 5

Codo
Codo

Reputation: 78835

This is a late addition. But the solution seems to be more elegant and easier to use.

It uses a pipelined function that has to be installed once:

CREATE TYPE number_row_type AS OBJECT 
(
  num NUMBER
);

CREATE TYPE number_set_type AS TABLE OF number_row_type;

CREATE OR REPLACE FUNCTION number_range(p_start IN PLS_INTEGER, p_end IN PLS_INTEGER)
    RETURN number_set_type
    PIPELINED
IS
    out_rec number_row_type := number_row_type(NULL);

BEGIN
  FOR i IN p_start .. p_end LOOP
    out_rec.num := i;
    pipe row(out_rec);
  END LOOP;

END number_range;
/

Then you can use it like this:

select * from table(number_range(1, 10));

NUM
---
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10

The solution is Oracle specific.

Upvotes: 1

Thiyagu ATR
Thiyagu ATR

Reputation: 2264

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.

Upvotes: 3

Gaspa79
Gaspa79

Reputation: 5689

I just did a table valued function to do this in SQL server, if anyone is interested, this works flawlessly.

CREATE FUNCTION [dbo].[NumbersBetween]
(
    @StartN int,
    @EndN int
)
RETURNS 
@NumberList table
(
    Number int
)

AS

BEGIN

WHILE @StartN <= @EndN
    BEGIN
    insert into @NumberList
    VALUES (@StartN)
    set @StartN = @StartN + 1
    END

Return

END
GO

If you run the query: "select * from dbo.NumbersBetween(1,5)" (w/o the quotes of course) the result will be

Number
-------
1
2
3
4
5

Upvotes: 0

anthian
anthian

Reputation: 11

Gary, to show the result that he explained, the model query will be:

SELECT c1 FROM DUAL MODEL DIMENSION BY (1 as rn)
MEASURES (1 as c1) RULES ITERATE (8) (c1[ITERATION_NUMBER]=ITERATION_NUMBER+3) ORDER BY rn

;)

I always use:

SELECT (LEVEL - 1) + 3 as result FROM Dual CONNECT BY Level <= 8

Where 3 is the start number and 8 is the number of "iterations".

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

You can use the MODEL clause for this.

SELECT c1 from dual
  MODEL DIMENSION BY (1 as rn)  MEASURES (1 as c1)
  RULES ITERATE (7)
  (c1[ITERATION_NUMBER]=ITERATION_NUMBER+7)

Upvotes: 4

Rob van Wijk
Rob van Wijk

Reputation: 17705

SQL> var N_BEGIN number
SQL> var N_END number
SQL> exec :N_BEGIN := 3; :N_END := 10

PL/SQL procedure successfully completed.

SQL>  select :N_BEGIN + level - 1 n
  2     from dual
  3  connect by level <= :N_END - :N_BEGIN + 1
  4  /

         N
----------
         3
         4
         5
         6
         7
         8
         9
        10

8 rows selected.

This uses the same trick as Tony's. Note that when you are using SQL*Plus 9, you have to make this query an inline view as Tony showed you. In SQL*Plus 10 or higher, the above is sufficient.

Regards, Rob.

Upvotes: 4

Tony Andrews
Tony Andrews

Reputation: 132580

This trick with Oracle's DUAL table also works:

SQL> select n from
  2  ( select rownum n from dual connect by level <= 10)
  3  where n >= 3;

         N
----------
         3
         4
         5
         6
         7
         8
         9
        10

Upvotes: 63

Amit
Amit

Reputation: 143

Or you can use Between

Select Column1 from dummy_table where Column2 Between 3 and 10

Upvotes: 1

Related Questions