Oracle SQL:Get All Integers Between Two Numbers

Get list of numbers in between two columns with key

a_horse_with_no_name-s solution would be

 SELECT distinct Key,(level + StartNum)-1 Num
   FROM Table1
  CONNECT BY (LEVEL +StartNum ) <= EndNum+1
  order by Key, Num

Output:

But I'd prefer creating a global temporary table and populate it from plsql, as the above method contains subsequent decarts on the table (thus the distinct required).
http://www.dba-oracle.com/t_temporary_tables_sql.htm

Generate subsequent numbers between number range with SQL

using the model clause:

SQL> create table foo1
  2  (id number,
  3  low_value number,
  4  high_value number);

Table created.

SQL>
SQL> insert into foo1 values (1,  3270200000, 3270200010);

1 row created.

SQL> insert into foo1 values (2,  3270210000, 3270210005);

1 row created.

SQL> insert into foo1 values (3,  10, 10);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> with foo as
  2   (select f.id, f.low_value, f.high_value, f.high_value - f.low_value
  3     range from foo1 f)
  4  select key, low_value, high_value, unique_value
  5    from foo
  6   model partition by(id as key)
  7         dimension by(0 as f)
  8         measures(low_value as unique_value, low_value, high_value, range)
  9         rules (unique_value [for f from 0 to range[0]  increment 1]  = low_value[0] + cv(f),
 10               low_value[for f from 0 to range[0]  increment 1] = low_value[0],
 11               high_value[for f from 0 to range[0]  increment 1] = high_value[0]);

       KEY  LOW_VALUE HIGH_VALUE UNIQUE_VALUE
---------- ---------- ---------- ------------
         1 3270200000 3270200010   3270200000
         1 3270200000 3270200010   3270200001
         1 3270200000 3270200010   3270200002
         1 3270200000 3270200010   3270200003
         1 3270200000 3270200010   3270200004
         1 3270200000 3270200010   3270200005
         1 3270200000 3270200010   3270200006
         1 3270200000 3270200010   3270200007
         1 3270200000 3270200010   3270200008
         1 3270200000 3270200010   3270200009
         1 3270200000 3270200010   3270200010
         2 3270210000 3270210005   3270210000
         2 3270210000 3270210005   3270210001
         2 3270210000 3270210005   3270210002
         2 3270210000 3270210005   3270210003
         2 3270210000 3270210005   3270210004
         2 3270210000 3270210005   3270210005
         3         10         10           10

18 rows selected.

SQL>

and 11g recursive factoring

SQL> with foo (id, low_value, high_value, unique_value)
  2        as (select f.id, f.low_value, f.high_value, low_value unique_value
  3              from foo1 f
  4             union all
  5            select id, low_Value, high_value, unique_value + 1
  6              from foo
  7             where unique_value < high_value)
  8  select id, low_value, high_value, unique_value
  9    from foo
 10   order by id, unique_value
 11  /

        ID  LOW_VALUE HIGH_VALUE UNIQUE_VALUE
---------- ---------- ---------- ------------
         1 3270200000 3270200010   3270200000
         1 3270200000 3270200010   3270200001
         1 3270200000 3270200010   3270200002
         1 3270200000 3270200010   3270200003
         1 3270200000 3270200010   3270200004
         1 3270200000 3270200010   3270200005
         1 3270200000 3270200010   3270200006
         1 3270200000 3270200010   3270200007
         1 3270200000 3270200010   3270200008
         1 3270200000 3270200010   3270200009
         1 3270200000 3270200010   3270200010

        ID  LOW_VALUE HIGH_VALUE UNIQUE_VALUE
---------- ---------- ---------- ------------
         2 3270210000 3270210005   3270210000
         2 3270210000 3270210005   3270210001
         2 3270210000 3270210005   3270210002
         2 3270210000 3270210005   3270210003
         2 3270210000 3270210005   3270210004
         2 3270210000 3270210005   3270210005
         3         10         10           10

18 rows selected.

SQL>

oracle 11g sql query obtains every number from set of ranges

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( A, B ) AS
SELECT 10, 12 FROM DUAL UNION ALL
SELECT 23, 25 FROM DUAL UNION ALL
SELECT 51, 55 FROM DUAL;

Query 1 Use a Recursive Sub-Query factoring clause (a.k.a. CTE):

WITH cte ( a, b ) AS (
  SELECT A, B FROM table_name
UNION ALL
  SELECT A + 1, B FROM cte WHERE A < B
)
SELECT a FROM cte

Results:

|  A |
|----|
| 10 |
| 23 |
| 51 |
| 11 |
| 24 |
| 52 |
| 12 |
| 25 |
| 53 |
| 54 |
| 55 |

Query 2 Use a correlated hierarchical query:

SELECT n.COLUMN_VALUE
FROM   table_name t
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT t.A + LEVEL - 1
             FROM   DUAL
             CONNECT BY t.A + LEVEL - 1 <= t.B
           ) AS SYS.ODCINUMBERLIST
         )
       ) n

Results:

| COLUMN_VALUE |
|--------------|
|           10 |
|           11 |
|           12 |
|           23 |
|           24 |
|           25 |
|           51 |
|           52 |
|           53 |
|           54 |
|           55 |

Select or print column values that don’t exist between two integers

You could do something like:

select level num
from   dual
connect by level <= 10000
minus
select column2
from   your_table;

You may need to do to_char(level) if your column2 is a varchar2 column.

Extract integer from a string in Oracle SQL and find and replace a specific character with another

To extract only number part please use '[^[:digit:]]' instead of '[[:alpha:]]' with regexp_replace()

regexp_replace(column_name, '[^[:digit:]]', '')

You can use below query to resolve your second problem.

select '"'||replace('Example"','"','""')||'"' from yourtable

Get even / odd / all numbers between two numbers

Something like this:

create table ranges (range varchar);

insert into ranges 
values
('1-9'),
('2-10'),
('11-20');

with bounds as (
  select row_number() over (order by range) as rn,
         range,
         (regexp_split_to_array(range,'-'))[1]::int as start_value,
         (regexp_split_to_array(range,'-'))[2]::int as end_value
  from ranges
)
select rn, range, string_agg(i::text, ',' order by i.ordinality)
from bounds b
  cross join lateral generate_series(b.start_value, b.end_value) with ordinality i
group by rn, range

This outputs:

rn | range | string_agg                   
---+-------+------------------------------
 3 | 2-10  | 2,3,4,5,6,7,8,9,10           
 1 | 1-9   | 1,2,3,4,5,6,7,8,9            
 2 | 11-20 | 11,12,13,14,15,16,17,18,19,20

Oracle compare two numbers in select

Here's one option:

SQL> set serveroutput on
SQL> declare
  2    retval varchar2(20);
  3  begin
  4    execute immediate q'[select case when 5 < 6 then 'true'
  5                                     else 'false'
  6                                end
  7                         from dual]'
  8            into retval;
  9
 10    dbms_output.put_Line('Result: ' || retval);
 11  end;
 12  /
Result: true

PL/SQL procedure successfully completed.

SQL>