0

I have below table with coulmn cityname,operatorname and prefix:- Prefix column contains multiple values with range of that.

Cityname operatorname     prefix
-------- ---------------- -----------------------------------------------------------
Kolkata  Unitech          90620-90629 82960-82969
Kolkata  Datacom          90730-90739
Kolkata  BSNL             94330-94339 94320-94325
Kolkata  Loop             91100-91109
Kolkata  Reliance Telecom 98830-98839 96810-96819 88200-88209
Mumbai   BPL Mobile       98210-98219 96640-96649 97730-97739 98700-98709 80820-80829
Mumbai   Bharti Airtel    98670-98679 98920-98929 99670-99674 99870-99878 90040-90049

but i want each preix on new row also there are range between for e.g. 91100-91109 means range from 91100,91101 91102 91103.....till 91109..

belwo is the output i want for e.g. kolkata Unitech Wireless

Cityname  operatorname      prefix 
--------  ----------------  ------
Kolkata   Unitech Wireless  90620
Kolkata   Unitech Wireless  90621
Kolkata   Unitech Wireless  90622
Kolkata   Unitech Wireless  90623
.....
.....
.....
Kolkata   Unitech Wireless  90629
Kolkata   Unitech Wireless  82960
Kolkata   Unitech Wireless  82961
Kolkata   Unitech Wireless  82962
.....
.....
.....
Kolkata   Unitech Wireless  82969

then followed by kolkata datacom solutions and so on...

Please need help to write down query in Sql server 2008

Please suggest as early as possible.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
Sushant Dalvi
  • 11
  • 1

1 Answers1

3

I can see this solved in two logical steps:

  1. Split every prefix range list into a row set of prefix ranges, i.e. every row like

    city operator prefix-range1 prefix-range2 prefix-range3 …

    gets split into

    city operator prefix-range1
    city operator prefix-range2
    city operator prefix-range3
    city …

  2. Expand every prefix range item like

    city operator Prefix1-PrefixN

    into a series of rows like this:

    city operator Prefix1
    city operator Prefix2
    city operator …
    city operator PrefixN-1
    city operator PrefixN

Below is an attempt at implementing the said logic:

WITH data (Cityname, operatorname, prefix) AS (  /* this is just a sample data definition */
  SELECT 'Kolkata', 'Unitech         ', '90620-90629 82960-82969' UNION ALL
  SELECT 'Kolkata', 'Datacom         ', '90730-90739' UNION ALL
  SELECT 'Kolkata', 'BSNL            ', '94330-94339 94320-94325' UNION ALL
  SELECT 'Kolkata', 'Loop            ', '91100-91109' UNION ALL
  SELECT 'Kolkata', 'Reliance Telecom', '98830-98839 96810-96819 88200-88209' UNION ALL
  SELECT 'Mumbai ', 'BPL Mobile      ', '98210-98219 96640-96649 97730-97739 98700-98709 80820-80829' UNION ALL
  SELECT 'Mumbai ', 'Bharti Airtel   ', '98670-98679 98920-98929 99670-99674 99870-99878 90040-90049'
),
SplitGroups AS (  /* this is where the list is split into separate ranges */
  SELECT
    d.Cityname, d.operatorname,
    StartPrefix = CAST(LEFT (x.PrefixGroup, 5) AS int),
    EndPrefix   = CAST(RIGHT(x.PrefixGroup, 5) AS int)
  FROM (
    SELECT
      Cityname, operatorname,
      prefixlist = CAST('<i>'+REPLACE(prefix, ' ', '</i><i>')+'</i>' AS xml)
    FROM data
  ) d
  CROSS APPLY (
    SELECT
      i.value('.', 'varchar(max)') AS PrefixGroup
    FROM d.prefixlist.nodes('i') x (i)
  ) x
)
SELECT  /* the final SELECT expands the ranges into single prefix rows */
  g.Cityname, g.operatorname,
  prefix = g.StartPrefix + v.number
FROM SplitGroups g
  INNER JOIN master..spt_values v on v.type = 'P'
    AND v.number BETWEEN 0 AND g.EndPrefix - g.StartPrefix

It works in my SQL Server 2008 R2 as expected, but the following assumptions have been made:

  1. All the original prefix values are formatted uniformly and consistently:

    • the ranges are separated by a single space;

    • there are no spaces apart from those separating the ranges;

    • every range is an integer, followed by a hyphen (-), followed by an integer.

  2. Every integer (prefix) contains exactly 5 digits.

  3. Every range spans no more than 2048 prefixes, i.e. the difference between the ending prefix and the starting prefix never exceeds 2047. This is the limitation of the master..spt_values table. You can replace it with your own numbers table if you need support for more than 2048 prefixes in a range.

References:

Community
  • 1
  • 1
Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • Very nice! Learnt quite a lot from this (would never have occured to me to cast to XML like that) - does it make more sense to use spt_values on ranges < 2047 than joining a tally table on? – Dibstar Aug 11 '11 at 13:56
  • Can't say for sure. If you mean whether it is safe (whether it will not break one day when the table is gone or its structure is changed), I personally wouldn't worry too much about that. The table is *very* old (has been inherited from Sybase) and thus has probably grown into the internals of the server considerably. … – Andriy M Aug 11 '11 at 14:22
  • As for the performance, I don't think the difference should be noticeable, if any. Still, if a tally table is used in many various cases throughout the project, I would possibly consider creating my own. You see, `spt_values` contains many other things apart from numbers, and my table would contain numbers only. The rows therefore would be narrower, thus potentially increasing the overall performance. But, again, it might go unnoticed if the table is not heavily employed. – Andriy M Aug 11 '11 at 14:24
  • it was more the security I was interested in (as I( don't currently have a tally table and um unlikely to get one) - as for the performance interesting to note that, will be something I use more often then :) – Dibstar Aug 11 '11 at 14:27
  • By the way, thanks for raising up the question. I forgot to add that to my 'assumption list' (the fact that the present solution has a limitation as to the supported number of prefixes in one range). – Andriy M Aug 11 '11 at 14:33