Expand a range of integers for joining SQL

Question

I have one table that stores a whole series of integers in a field, sort of like a print range (for example, "1-2,4-7,9-11"). This field may also contain one number.

My goal is to attach this table to a second one that has discrete values ​​instead of ranges.

So, if the table contains

1-2,5 9-15 7 

And the second table contains

 1 2 3 4 5 6 7 8 9 10 

The result of the merger will be

 1-2,5 1 1-2,5 2 1-2,5 5 7 7 9-15 9 9-15 10 

Work in SQL Server 2008 R2.

Answer 1

Use the line split function of your choice to separate by comma. Find out the min / max values ​​and use the connection between them.

SQL Fiddle

Setting up MS SQL Server 2012 schema :

 create table T1(Col1 varchar(10)) create table T2(Col2 int) insert into T1 values ('1-2,5'), ('9-15'), ('7') insert into T2 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) 

Request 1 :

 select T1.Col1, T2.Col2 from T2 inner join ( select T1.Col1, cast(left(S.Item, charindex('-', S.Item+'-')-1) as int) MinValue, cast(stuff(S.Item, 1, charindex('-', S.Item), '') as int) MaxValue from T1 cross apply dbo.Split(T1.Col1, ',') as S ) as T1 on T2.Col2 between T1.MinValue and T1.MaxValue 

Results :

 | COL1 | COL2 | ---------------- | 1-2,5 | 1 | | 1-2,5 | 2 | | 1-2,5 | 5 | | 9-15 | 9 | | 9-15 | 10 | | 7 | 7 | 

Answer 2

As everyone said, it hurts to do this in SQL Server. If you should , I think this is the right approach.

First, define your own rules for parsing the string, then break the process down into clearly defined and understandable problems.
Based on your example, I think this is a process:

• Separate values, separated by commas, per line per line
• If the data does not contain , it ends (this is a separate value)
• If there is a dash in it, analyze the left and right sides of the dash
• Given that the left and right sides (range) determine all the values ​​between them in a row

I would create a temporary table to populate the parsing results that need two columns:
SourceRowID INT, ContainedValue INT

and the other for intermediate processing:
SourceRowID INT, ContainedValues VARCHAR

Separate comma separated values ​​into your own lines using CTE, as this Step 1 is now a well-defined and understandable problem to solve :

Include comma separated line in separate lines

So your result from the source is '1-2,5'
will be:
'1-2'
'5'

From there, the SELECT from this processing table, in which the field does not contain , contains a dash. Step 2 is now a well-defined and understandable problem to solve . These are stand-alone numbers and can go directly to the temp results table. The result table should also have an identifier reference to the source row.

Next, analyze the values ​​to the left and right of the dash using CHARINDEX to find it, then the corresponding LEFT and RIGHT functions will be executed as needed. This will give you the start and end value.

Here's an important question to solve this step 3 - this is now a clearly defined and understandable problem to solve :

T-SQL Substring - Separating First and Last Name

Now you have separated the start and end values. Use another function that can explode this range. Step 4 is a clear and understandable problem to solve :

SQL: create a sequential list of numbers from different launch points

SELECT all N between @min and @max

What is the best way to create and populate a number table?

and also insert it into the temp table.

Now what you need is a temporary table with each value in an exploded range.

Just JOIN that in another table of values ​​now, and then to the source table by reference ID and you are there.

Answer 3

My suggestion is to add another field and many more entries to the range table. In particular, the main key will be an integer, and the other field will be a range. The entries will look like this:

 number range 1 1-2,5 2 1-2,5 3 na 4 na 5 1-2,5 

etc.

Having said that, this is still quite limiting, since a number can have only one range. If you want to be solid, set up many, many relationships between numbers and ranges.

Answer 4

As far as I can tell, the best option is something like below:

Create a table value function that takes your ranges, converts them to an int collection. Therefore 1-3,5 will return:

 1 2 3 5 

Then use these results to join other tables. I don't have the exact function to do this at hand, but this one seems like a great start.