TSQL Tutorial : Filtering Data

You use the SQL WHERE clause to qualify the data a SELECT statement returns. It can also be used to

Limit the rows affected by an UPDATE or DELETE statement. Here are some queries that use WHERE to

filter the data they return:

SELECT UPPER(LastName), FirstName

FROM customers

WHERE State='TX'

FirstName

--- ---------

DOE John

DOE Jane

The following code restricts the customers returned to those whose address contains the word "Joshua."

SELECT LastName, FirstName, StreetAddress FROM customers

WHERE StreetAddress LIKE '%Joshua%'

LastName FirstName StreetAddress

-------- --------- ---------------

Doe John 123 Joshua Tree

Doe Jane 123 Joshua Tree

Note the use of "%" as a wildcard. The SQL wildcard % (percent sign) matches zero or more instances of

any character, while _ (underscore) matches exactly one.

Here's a query that returns the orders exceeding $500:

SELECT OrderNumber, OrderDate, Amount

FROM orders

WHERE Amount > 500

OrderNumber OrderDate Amount

----------- ----------------------- --------

102 1992-02-27 00:00:00.000 678.90

103 1995-05-20 00:00:00.000 86753.09

104 1997-11-21 00:00:00.000 678.90

The following example uses the BETWEEN operator to return orders occurring between October1990 and

May1995, inclusively. I've included the time with the second of the two dates because, without it, the time

would default to midnight (SQL Server datetime columns always store both the date and time; an omitted

time defaults to midnight), making the query noninclusive. Without specification of the time portion, the

query would return only orders placed up through the first millisecond of May31.

SELECT OrderNumber, OrderDate, Amount FROM orders

WHERE OrderDate BETWEEN '10/01/90' AND '05/31/95 23:59:59.999'

OrderNumber OrderDate Amount

----------- ----------------------- --------

101 1990-10-18 00:00:00.000 123.45

102 1992-02-27 00:00:00.000 678.90

103 1995-05-20 00:00:00.000 86753.09

TSQL Tutorial..