views:

111

answers:

1

Hi I want to select values from tables by passing two parameters like start and end ranges

How can i get it?

Ex:

sid     sname

s001    name1 
s002    name2
s003    name3 
s004    name4
s005    name5 
s006    name6
s007    name7 
s008    name8
s009    name9 
s0010   name10

here i want to pass two values like 3 and 5 so that my query will return results like

s003  name3
s004  name4
s005  name5

Thank you

+1  A: 

If you're using SQL Server, and assuming your columns are both VARCHAR, and your "sid" column is always "s00" + number, then you could do something like this:

declare @value1 int
declare @value2 int

set @value1 = 3
set @value2 = 5

select
  sid, sname
from
  (your table name)
where
  sid between 's00' + CAST(value1 AS VARCHAR(3)) AND
              's00' + CAST(value2 as VARCHAR(3))

Marc

UPDATE:
If your rule is that the "SID" columns is always just a single letter "s" (or something else) and then any number of digits (e.g. "s1", "s012", "s292929"), then this query would do the trick:

declare @value1 int
declare @value2 int

set @value1 = 12
set @value2 = 15

select
  sid, sname
from
  tbl2
where
  CAST(SUBSTRING(sid, 2, 999) AS INT) BETWEEN @value1 and @value2

Whatever your pattern and rule is - you can find a query that works - but you need to know what your data looks like and how to query it.

marc_s
I'm not sure the values will be in sequential order like s001, s002 and s003 they may have like s012, s015, s020 like this. So will it work for me?
Nagu
It depends. In your example, you have S0010, in your comment you have S012. If your sid column is always formatted as S00<number>, this query will work.
Lieven
@nagu: well, there has to be SOME pattern or something in order to query - you can't query for something you don't know how it's going to look like.....
marc_s
This query will work for anything that starts with "s00......." - if your values are different, you'll have to adapt the query. The query can't just magically **know** what your data looks like - you'll have to know that and apply that knowledge to how you query your data.
marc_s
So is the rule that the "SID" field will always be a single "s" at the beginning and then only (three, four, possibly more) digits after that?
marc_s