OraQA

Question: how to generate a range between numbers?

January 23rd, 2008 By bssa

Given this input:

operator_name          numbers              loc
-------------------------------------------------------
sony              9848930997-9848931000   pune
o2                 65432-65435             hyd
voda              369852-369855             mum
orange            9290988345-9290988345   blore

How to write a query to retrieve the data like this:

operatorname               numbers                  loc
----------------------------------------------------------
sony                        9848930997                pune
sony                        9848930998                pune
sony                        9848930999                pune
sony                        9848931000                pune
o2                            65432                    hyd
o2                            65433                    hyd
o2                            65434                    hyd
o2                            65435                    hyd
voda                         369852                     mum
voda                         369853                     mum
voda                         369854                     mum
voda                         3698525                   mum
orange                      9290988345               blore

Thanks for your help.

Filed under Question, SQL. Follow any responses to this entry through this RSS 2.0 feed.

One Response to “Question: how to generate a range between numbers?”

mjb Says:
January 24th, 2008 at 6:47 am

Ok, so I have a table that looks like this:

SQL> desc testit
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OPERATOR_NAME                                      VARCHAR2(10)
 NUMBERS                                            VARCHAR2(50)
 LOC                                                VARCHAR2(10)

And, my input dataset looks like this:

SQL> select * from testit;

OPERATOR_N NUMBERS                                            LOC
---------- -------------------------------------------------- ----------
sony       9848930997-9848931000                              pune
o2         65432-65435                                        hyd
voda       369852-369855                                      mum
orange     9290988345-9290988345                              blore

One solution, though I’m not convinced it’s optimal, is this:

SQL> l
  1  with source_data as
  2  (select operator_name,
  3          substr(numbers,1,instr(numbers,'-')-1) start_num,
  4          substr(numbers,instr(numbers,'-')+1) end_num,
  5          loc
  6     from testit)
  7  select distinct operator_name,
  8                  start_num+level-1 numbers,
  9                  loc
 10    from dual,
 11         source_data
 12   connect by level <=end_num-start_num+1
 13* order by 1,2,3
SQL> /

OPERATOR_N    NUMBERS LOC
---------- ---------- ----------
o2              65432 hyd
o2              65433 hyd
o2              65434 hyd
o2              65435 hyd
orange     9290988345 blore
sony       9848930997 pune
sony       9848930998 pune
sony       9848930999 pune
sony       9848931000 pune
voda           369852 mum
voda           369853 mum
voda           369854 mum
voda           369855 mum

13 rows selected.

You must be logged in to post a comment.

RSS feed for comments on this question

« How to find missing alphanumeric characters in SQL

How to solve the Coin Combinations Puzzle in SQL »

Oracle Question and Answer

Categories

Links

Oracle News

Question: how to generate a range between numbers?

One Response to “Question: how to generate a range between numbers?”

Leave a Reply