How do I search on a number range? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How do I search on a number range?

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-09 : 08:27:02

select * from table
where column in ['102' ... '204']

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 08:33:32

WHERE Column BETWEEN '102' AND '204'

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-09 : 08:33:43

If you use IN you must provide specific values like this: WHERE column in (102, 109, 147, 204)
If you want a range you can use BETWEEN: WHERE column BETWEEN 102 AND 204

- Lumbago

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-09 : 08:34:37

You can also use regular expressions but I was never really good with those...

- Lumbago

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 12:21:52

or use >< operators


select * from table 
where column>=102 and column <=204

kishore_pen
Starting Member

49 Posts

Posted - 2009-06-16 : 07:33:14

use between operator in your where clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 07:48:40

Both Lumbago and Visakh16 will get you the correct result, with the restriction that data in table can be interpreted as numeric.
My suggestion will give you extra data.

DECLARE	@Sample TABLE
	(
		Col1 VARCHAR(20)
	)

INSERT	@Sample
SELECT	'105' UNION ALL
SELECT	'1075'

-- Peso
SELECT	*
FROM	@Sample
WHERE	Col1 BETWEEN '102' AND '204'

-- Lumbago
SELECT	*
FROM	@Sample
WHERE	Col1 BETWEEN 102 AND 204

-- Visakh16
SELECT	*
FROM	@Sample
WHERE	Col1 >= 102
	AND Col1 <= 204

However, this is easy to fix and also will hold for bad data. The other suggestion will not.

DECLARE	@Sample TABLE
	(
		Col1 VARCHAR(20)
	)

INSERT	@Sample
SELECT	'bad' UNION ALL
SELECT	'105' UNION ALL
SELECT	'1075'

-- Peso
SELECT	*
FROM	@Sample
WHERE	Col1 BETWEEN '102' AND '204'
	AND LEN(Col1) = 3

-- Lumbago
SELECT	*
FROM	@Sample
WHERE	Col1 BETWEEN 102 AND 204

-- Visakh16
SELECT	*
FROM	@Sample
WHERE	Col1 >= 102
	AND Col1 <= 204

It's all about which datatype you use for your column used in the FILTER.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources