Transact-SQL Functions

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 name does not begin with “Jess.”

SELECT Name

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:

SELECT Name

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

313

Chapter 8: Speaking SQL

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 user-defined 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.

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, or, in other words, it rounds down the value that was passed in. The following query will return the value 5:

SELECT FLOOR(5.5)

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

314

String Functions

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.

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)

FROM Employees

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 leftor right-hand 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')

315

Chapter 8: Speaking SQL

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:

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

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

FROM Employees

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

FROM Employees

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 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 expect.

316