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
--

q; output to a text file

Is it possible to send the output of a query to a text file in a stored
procedure? When I run stored procedure in Query Analyzer I am able to do that
and I am wondering if this is possible in a automated way?Not directly. But indirectly, you can use xp_cmdshell and BCP or OSQL. Note that the file is created
by the database server. If you are on 2005, you can also call an Assembly, if that suits you better.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:33A04A73-5811-4479-A181-67A3F999C770@.microsoft.com...
> Is it possible to send the output of a query to a text file in a stored
> procedure? When I run stored procedure in Query Analyzer I am able to do that
> and I am wondering if this is possible in a automated way?|||In order to output to a file, you would have to use ( xp_cmdshell and OSQL )
OR bcp.
Check [ BCP Utility ] or [OSQL ] in Book Online
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:33A04A73-5811-4479-A181-67A3F999C770@.microsoft.com...
> Is it possible to send the output of a query to a text file in a stored
> procedure? When I run stored procedure in Query Analyzer I am able to do
> that
> and I am wondering if this is possible in a automated way?

q; output to a text file

Is it possible to send the output of a query to a text file in a stored
procedure? When I run stored procedure in Query Analyzer I am able to do tha
t
and I am wondering if this is possible in a automated way?Not directly. But indirectly, you can use xp_cmdshell and BCP or OSQL. Note
that the file is created
by the database server. If you are on 2005, you can also call an Assembly, i
f that suits you better.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:33A04A73-5811-4479-A181-67A3F999C770@.microsoft.com...
> Is it possible to send the output of a query to a text file in a stored
> procedure? When I run stored procedure in Query Analyzer I am able to do t
hat
> and I am wondering if this is possible in a automated way?|||In order to output to a file, you would have to use ( xp_cmdshell and OSQL )
OR bcp.
Check [ BCP Utility ] or [OSQL ] in Book Online
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:33A04A73-5811-4479-A181-67A3F999C770@.microsoft.com...
> Is it possible to send the output of a query to a text file in a stored
> procedure? When I run stored procedure in Query Analyzer I am able to do
> that
> and I am wondering if this is possible in a automated way?