Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition

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 2

3

:

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 left- or

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

String Functions

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, begin-

ning at a specified start position and spanning a specified number of charac-

ters.

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

Chapter 8: Speaking SQL