Ask the Expert

Splitting number ranges

I am having hard time trying to split number ranges. The following three examples would help explain better. If we can do this in SQL it would be great, if not, a procedure or function would also be fine. I have tried both, but cannot seem to handle all the cases as described below. Could you please help me?

The table with two columns, serial_start and serial_end, has the following data:

SERIAL_START   SERIAL_END
-------------  ---------- 
40             140
60             145
100            110
150            250

User Input: Serial_Start = 30, Serial_End = 40

Expected Output: 30-39 
When a user inputs a serial_begin=30 and serial_end=40, I have to look into this table and say, OK, the only serials that can be assigned are 30 - 39. This is because 40 is already used in the table.

Another example: Same table has the following data:

SERIAL_START   SERIAL_END
-------------  ---------- 
200            230

User Input: Serial_Start = 150, Serial_End = 250

Expected Output: 150 - 199
                 231 - 250  
When a user inputs a serial_begin=150 and serial_end=250, I have to look into this table and say, OK, the only serials that can be assigned are 150 - 199 and 231 - 250. This is because 200 - 230 are already used in the table.

Another example: Same table has the following data:

SERIAL_START   SERIAL_END
-------------  ---------- 
50             98
100            150
160            190
200            230

User Input: Serial_Start = 90, Serial_End = 120

Expected Output: 99 - 99


    Requires Free Membership to View

Looks like you also submitted this question to the "Ask Tom" site and he beat us to the answer. For anyone who's interested, you can find the solution to this question here.

For More Information


This was first published in February 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: