Build Your Own ASP.NET 3.5 Website Using C# & VB (60 page)

>

The greater-than operator is used in WHERE clauses to determine whether

the first value is greater than the second. For example, the following query

would return all the records from the table whose EmployeeID is greater

than ten (that is, 11 and up):

FROM Employees

WHERE EmployeeID > 10

<

The less-than operator is used in WHERE clauses to determine whether the

first value is less than the second. The result of the following query would

return from the table all records whose EmployeeID is less than ten (that

is, nine and lower):

FROM Employees

WHERE EmployeeID < 10

>=

The greater-than or equal-to operator is used in WHERE clauses to determine

whether the first value is greater than, or equal to, the second. The following query would return the record with an EmployeeID of ten, and every one after that:

FROM Employees

WHERE EmployeeID >= 10

Licensed to [email protected]

Speaking SQL

327

<=

The less-than or equal-to operator is used in WHERE clauses to determine

whether the first value is less than, or equal to, the second. The result of

the following query would be the record with EmployeeID of ten, and

every one before that:

FROM Employees

WHERE EmployeeID <= 10

<>, !=

This operator is used to check whether a value is not equal to a second.

OR

This operator is used with the WHERE clause in the SELECT statement. The

OR operator can be used when a certain condition needs to be met, or

when only one of two conditions needs to be met. For example, the following query’s results would return the employees with employee IDs of 1 or 2:

FROM Employees

WHERE EmployeeID = 1 OR EmployeeID = 2

AND

This operator works just like OR, except that it requires
all
of the conditions

to be satisfied, not just any of them.

NOT

Typically used in conjunction with the LIKE operator, the NOT operator

is used when we’re looking for values that are not like the value we specify. For example, the following query would return all employees whose names do not begin with “Jess:”

FROM Employees

WHERE Name NOT LIKE 'Jess%'

_, ?

The underscore operator is used by SQL Server in WHERE clauses, and

matches any single character in a string. For instance, if you weren’t sure

of the first letter of Geoff Kim’s surname, you could use the following

query:

Licensed to [email protected]

328

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

FROM Employees

WHERE Name LIKE 'Geoff _im'

This would return Geoff Kim’s record, as well as Geoff Sim’s, Geoff Lim’s,

and so on, were there such employees in the database. Note that the _

character only matches a single character, so Geoff Sirrim would not be

returned. To match zero or more characters, you’d use the % or * operator.

%, *

The multiple character operator is similar to the underscore operator,

except that it matches multiple or zero characters, whereas the underscore

operator only matches one.

IN

This operator is used in WHERE clauses to specify that an expression’s

value must be one of the values specified in a list.

Transact-SQL Functions

As well as using operators to construct expressions manually, SQL Server provides

us with some functions that we can use within our queries. For the most part, SQL

has sufficient functions to handle almost all of the day-to-day tasks that you’ll undertake. So let’s take a look at some of the most useful and common functions you’re likely to use in your queries.

Getting More Information

Note that the complete list of built-in functions supported by T-SQL is much

longer than that presented here; you can find the complete lists by searching for,

say, “string functions” or “date and time functions” in the free SQL Server documentation, SQL Server Books Online, which can be downloaded from
Microsoft’s

TechNet site.
1 Additionally, SQL Server allows you to create your own userdefined functions either in SQL, or a language such as VB or C#. However, this is an advanced topic that we won’t be covering in this book.

1 http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Licensed to [email protected]

Speaking SQL

329

Arithmetic Functions

SQL supports many arithmetic functions. Although the commonly preferred solution

is to perform such calculations in VB or C# code, SQL’s arithmetic functions can

prove handy at times.

ABS

This function returns the absolute value. Both of the following queries will return

the value 5:

SELECT ABS(5)

SELECT ABS(-5)

CEILING

CEILING returns the smallest integer that’s greater than the value that was passed

in. In other words, this function rounds up the value passed in. The following

query will return 6:

SELECT CEILING(5.5)

FLOOR

This function returns the largest integer that’s less than the value that was passed

in; in other words, it rounds down the value that was passed in. The following

query will return the value 5:

SELECT FLOOR(5.5)

MOD

MOD returns the remainder of one value divided by another. The following query

would return the value 2:

SELECT MOD(8, 3)

SIGN

This function returns -1, 0, or 1, to indicate the sign of the argument.

Licensed to [email protected]

330

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

POWER

This function returns the result of one value raised to the power of another. The

following query returns the result of 23:

SELECT POWER(2, 3)

SQRT

SQRT returns the non-negative square root of a value.

Many, many more mathematical functions are available—check SQL Server Books

Online for a full list.

String Functions

String functions work with literal text values rather than numeric values.

UPPER, LOWER

This function returns the value passed in as all uppercase or all lowercase, respectively. Take the following query as an example: SELECT LOWER(Username), UPPER(State)

The query above will return a list of usernames in lowercase, and a list of states

in uppercase.

LTRIM, RTRIM

This function trims whitespace characters, such as spaces, from the left-or righthand side of the string, respectively.
REPLACE

Use the REPLACE function to change a portion of a string to a new sequence of

characters that you specify:

SELECT REPLACE('I like chocolate', 'like', 'love')

This query will search the string “I like chocolate” for the word “like” and replace it with the word “love,” as shown in the output below: Licensed to [email protected]

Speaking SQL

331

-----------------------------------------------------I love chocolate

(1 row(s) affected)

SUBSTRING

This function returns the sequence of characters within a given value, beginning

at a specified start position and spanning a specified number of characters:

SELECT SUBSTRING('I like chocolate', 8, 4)

The above query will take four characters from the string “I like chocolate”

starting from the eighth character, as shown in the output below:

---choc

(1 row(s) affected)

LEN

This function returns the length of a string. Thus, the following query would

return a list of all usernames, and how many characters were in each username:

SELECT Username, LEN(Username) AS UsernameLength

CHARINDEX

This function returns the first position in which a substring can be found in a

string.

It’s also worth noting that these functions can be used in conjunction with other

functions, often to create quite powerful results. For example, the following SQL

query would return the first name of every employee within the Employees table:

SELECT SUBSTRING(Name, 1, CHARINDEX(' ', Name)) AS FirstName

Here, we’re using two string functions. CHARINDEX is used to locate the first space

within the Name column. If we assume that the first space indicates the end of the

Licensed to [email protected]

332

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

first name, we can then use SUBSTRING to extract the first name from the name string.

The results, shown in
Figure 8.9
, are as we’d expect.

Figure 8.9. Employees’ first names

Note that the query isn’t bulletproof—it’s only suitable for western-style names. If

an employee had no spaces in his or her name (imagine, for instance, that we hired

Cher to work as an Engineer), then the CHARINDEX function would return -1, indicating that there was no space character in the name. The SUBSTRING function would then return NULL, so the list of results would be flawed.

Date and Time Functions

Date and time functions facilitate the manipulation of dates and times that are stored

within your database. These functions work with arguments of the datetime type.

Here are some of the most useful ones:

GETDATE

returns the current date and time

DATEADD

adds an interval to an existing date (a number of days, weeks, etc.) in order to

obtain a new date

DATEDIFF

calculates the difference between two specified dates

Licensed to [email protected]

Speaking SQL

333

DATEPART

returns a part of a date (such as the day, month, or year)

DAY

returns the day number from a date

MONTH

returns the month number from a date

YEAR

returns the year from a date

We won’t be working with these functions in our example application, but it’s good

to keep them in mind. Here’s a quick example that displays the current year:

SELECT YEAR(GETDATE())

The result (assuming it’s still 2008, of course) is shown below:

CurrentYear

----------2008

(1 row(s) affected)

Working with Groups of Values

Transact-SQL includes two very useful clauses that handle the grouping of records,