S.Das S.Das - 2 years ago 70
SQL Question

Selecting a range from table to which given number belongs

I have the following table:

Please see Image

Now what I want to do is find out which range a given number (suppose 4) belongs. And pick the corresponding


Any ideas how to do it using asp.net? I"m a newbie and don't know how to proceed.

Answer Source

It is very unfortunate that the pole height range is stored that way. It makes for some ugly SQL. You need to use substring approach to extract out the low and high range then do a BETWEEN. Assuming you are using MSSQL:

SET @nbr = 4;
SELECT Trans_Charges, Handling_Charges
FROM [YourTable]
WHERE @nbr BETWEEN CONVERT(INT, SUBSTRING(Pole_Height_Range,1,(CHARINDEX('-', Pole_Height_Range) - 1))) AND 
    CONVERT(INT, SUBSTRING(Pole_Height_Range,(CHARINDEX('-', Pole_Height_Range) + 1), LEN(Pole_Height_Range)));
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download