I have a list of number ranges and need to convert them to a list of all numbers in these ranges. I am using an MS SQL database

I have a table with the bellow information

I would like a SQL method to expand the ranges to individual numbers, one per row like below.

Ideally as a CTE without the need for temp tables.

Does anyone have a working solution?

CodePudding user response：

Simple recursive cte version. Let the cte generate all numbers from smallest RangeStart to largest RangeEnd. JOIN:

WITH RECURSIVE cte(n) as
(
   select min(RangeStart) from tbl
   UNION ALL
   SELECT n   1
   FROM cte
   WHERE n <= (select max(RangeEnd) from tbl)
)
select n
from cte
join tbl on n between RangeStart and RangeEnd

If your columns are character instead of numeric, add some cast's:

WITH RECURSIVE cte(n) as
(
   select min(cast(RangeStart as bigint)) from tbl
   UNION ALL
   SELECT n   1
   FROM cte
   WHERE n <= (select max(cast(RangeEnd as bigint)) from tbl)
)
select '0' || cast(n as varchar(15))
from cte
join tbl on n between cast(RangeStart as bigint)
                  and cast(RangeEnd as bigint)

CodePudding user response：

Without the Vertica TIMESERIES clause (PostGreSQL can also generate a series out of nothing, but differently) , try this:

WITH
-- input ..
indata(Range_Start,Range_End) AS (
          SELECT '01135489651','01135489750'
UNION ALL SELECT '01142225487','01142225686'
UNION ALL SELECT '01159857992','01159858090'
)
-- REAL query starts here, replace following comma with "WITH"
,
-- max range size is just under 200, so need a list of 200 consecutive integers ..
-- create a table "units" with integers 0 through 9 ...
units(unit) AS (
            SELECT 0
  UNION ALL SELECT 1
  UNION ALL SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 4
  UNION ALL SELECT 5
  UNION ALL SELECT 6
  UNION ALL SELECT 7
  UNION ALL SELECT 8
  UNION ALL SELECT 9
)
,
-- cross join integers 0 through 9 with each other three times and limit the 
-- list for the hundreds to 0 and 1, leaving you with 0 through 199 ....
i(i) AS (
  SELECT
    h.unit * 100   d.unit * 10   u.unit
  FROM       units h
  CROSS JOIN units d
  CROSS JOIN units u
  WHERE h.unit < 2
)
SELECT
   '0'
 ||CAST (
     CAST(range_start AS INTEGER)   i
   AS VARCHAR(16)
   ) AS result
FROM       indata
CROSS JOIN i
WHERE CAST(range_start AS INTEGER)   i <= CAST(range_end AS INTEGER)
ORDER  BY result
;

CodePudding user response：

as you don't have more then 200 numbers to compute, only one simple select query can be used with values and cross apply :

select   
         [Range Start]
        ,[Range End]
        ,[n]      = n1   n10   n100
        ,[number] = [Range Start]   n1   n10   n100
from (values(01135489651,01135489750),(01142225487,01142225686),(01159857992,01159858090))ranges([Range Start],[Range End])
CROSS APPLY (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n1(n1)
CROSS APPLY (values(0),(10),(20),(30),(40),(50),(60),(70),(80), (90))n10(n10)
CROSS APPLY (values(0),(100))n100(n100)
where 
    n1 n10 n100 BETWEEN 0 and [Range End] - [Range Start]

CodePudding user response：

you can create a table in your database with following syntax that provide you with a very large values

SELECT TOP 1000000000000
ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY A.name) AS 'Range'
into numbers --- a number table with list of values
FROM syscolumns A, syscolumns B 

after that use the following to create table in your database

SELECT *   INTO  your_new_table
from numbers
where range between 01135489651 and 01135489750
union 
select range
from numbers
where range between 01142225487 and 01142225686
union 
select range
from numbers
where range between 01159857992 and 01159858090