Use SQL to search a column for a range of values — Parv The IT Geek

Have you ever wanted to search for a column for a range of values? For example if you were searching for Product ID which ranges from 1 to 300, or look for a range of names starting A to D.

Well I can show you how to do these quickly and easily by using the power of SQL.

The below query shows there are 504 rows in the Production.Product table

USE AdventureWorks2012 
GO
SELECT *
FROM [Production].[Product]

If you wanted to search for ProductID’s which were 1 to 4 you could do it using the below query.

USE AdventureWorks2012 
GO
SELECT *
FROM [Production].[Product]
WHERE ProductID = 1
OR ProductID = 2
OR ProductID = 3
OR ProductID = 4

Whilst the above query works it is a very ugly and inefficient query as you are writing way too much code when you could use search a range which takes less time to type and is more easily read and understood.

SELECT * 
FROM [Production].[Product]
WHERE ProductID
BETWEEN 1 AND 4

As you can see the above query is much more readable , easily understood and more importantly is shorter which saves you time.

I would recommend using the syntax on different values and tables to see how many different types of data you can retrieve.


Originally published at https://parvtheitgeek.com on January 24, 2014.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: