S.Das S.Das - 5 months ago 9
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

Trans_Charges
and
Handling_Charges
.

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

Answer

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:

DECLARE @nbr INT;
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)));