CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Nagu
Nagu

Reputation: 5114

select query by range

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

Upvotes: 1

Views: 877

Answers (1)

marc_s
marc_s

Reputation: 755451

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.

Upvotes: 1

Related Questions