Query

I have a table
ID Range
1 13-17
2 18-24
3 25-34
I want to find the ID by supplying the say 23. The output in this case will
be ID= 2. Can I achieve this with a single query?
Thanks.
DECLARE @.SearchFor INT
SET @.SearchFor =3D 23
Select ID from TableName
Where @.SearchFor BETWEEN
SUBSTRING(Range,1,CHARINDEX('-',Range)-1) AND
SUBSTRING(Range,CHARINDEX('-',Range)+1,(LEN(RANGE)-CHARINDEX('-',Range))
If you don=B4t post DDL, all samples are untested.
HTH (anyway), Jens Suessmeyer.
http://www.sqlserver2005.de
--|||If this is a one-time query, then try Jens's suggestion.
Otherwise, I would suggest you add columns to the table with the integer
value of the extrema. After that, your table + data could look like
this:
ID Range Low High
1 13-17 13 17
2 18-24 18 24
3 25-34 25 34
You could use a variation of Jens's query to update the table with the
Low and High value. After that, you simply use the query
SELECT ID FROM ... WHERE 23 BETWEEN Low AND High
Gert-Jan
XXX wrote:
> I have a table
> ID Range
> 1 13-17
> 2 18-24
> 3 25-34
> I want to find the ID by supplying the say 23. The output in this case wil
l
> be ID= 2. Can I achieve this with a single query?
> Thanks.|||Anyway if you will go these approach, I would get rid of the range
column, because the information would be stored redundant in the column
because it can be careted anytime from the new columns Low/High
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--