SQL Functions Week 2

SQL Functions Week 2 - SQL Functions String Functions |...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
SQL Functions String Functions | Date Functions | Convert() Function | IsNull() Function | Aggregate Functions | Group By | HAVING Clause | Combining Functions This week we will look at how to use functions in our SELECT statements. Functions are used for a number of purposes: to change the format of our data for display, to perform calculations, or to manipulate character data. Functions allow us to provide data from the database in response to more complex business problems. They also will allow us to produce summarized data in the form of maximum, minimum, and average values. String Functions String functions allow us to manipulate string fields and present the data in different ways. For example, the Consumers table in the Coffee Merchant database has a field for LastName and a field for FirstName. Last week we combined those fields into one column and changed the way we output the data by displaying just the first letter of the first name, using the Left() function: SELECT LastName + ', ' + Left(FirstName, 1) as "Full Name" FROM Consumers ORDER BY LastName; A space is a valid character. If you want a space in the output, you must include it inside single quotes: ', ' . Also, remember that alias columns, such as the "Full Name" column alias in SELECT LastName + ', ' + Left(FirstName, 1) as "Full Name" MUST be in quotation marks if the column alias contains a space. If the column alias doesn't contain a space, then the column alias doesn't need quotes. For example, Select LastName + ', ' + FirstName as Student doesn't require the world Student in quotes, because there are not spaces in the word Student. The Left() function extracts characters from the left-hand side of a string, while Right() extracts characters from the right-hand side of a string. To extract characters from the middle of a string, use Substring(). This function has 3 arguments: the string you are extracting characters from, the position of the first character you want to extract, and the number of characters to extract. The Phone number in the Consumers table includes the area code and is in the format: (501) 555-6079. If we would like to see a list of area codes where our consumers are located, we need to extract 3 characters beginning at character position 2: SELECT DISTINCT Substring(Phone, 2, 3) as "Area Code" FROM Consumers ORDER BY 1; Remember, the DISTINCT keyword will suppress duplicate area codes from being returned – we only want to see each area code once in the list. Without DISTINCT we would return the area code for every consumer even if many consumers live in the same area code. Date Functions There are a number of very useful functions that allow us to extract various parts of a date and to do mathematical calculations on dates. Let’s review some of them.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Year('09/30/2008') will return 2008. Month('09/30/2008') will return 9.
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 5

SQL Functions Week 2 - SQL Functions String Functions |...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online