Functions

• The following functions can be made on values in a column: "AVG()," "COUNT()," "FIRST()," "LAST()," "MAX()," "MIN()," and "SUM()." The following functions are used to return a single value: "UCASE()" and "LCASE()" convert the case of the field; "MID()" returns text from the string; "LEN()" returns the number of characters; "ROUND()" rounds a numerical value; "NOW()" returns the system date and time, and "FORMAT()" changes how the field is displayed.
  
  

Function Examples

• All functions start with "SELECT." If in a table named "People," and you want to know is the average from the "Age" column, use the following statement:
  
  SELECT AVG(Age) AS AgeAverage FROM People
  
  Functions can be combined with "WHERE," to return more specific information. Imagine you want to identify which of your customers have made a purchase whose "Price" was above average, from a table called "Orders." You could use the following SQL statement:
  
  SELECT Customer FROM Orders
  
  WHERE Price>(SELECT AVG(Price) FROM ORDERS)
  
  

Wildcards

• Wildcards are used within searches. A "%" sign indicates a substitute for any number of characters. An underscore "_" is a substitute for a single character. To limit substitution to a defined range of characters, use square brackets. For example, [abc] would return any entry with an a, b or c in that position. You can also search for characters not in a list -- [!abc] and [^abc] can both be used to return an entry that does not have an a, b or c in that position.
  
  

Wildcard Examples

• You want to find a customer but you don't remember their name. You know it started with "So." Use the following search:
  
  SELECT * FROM Customer
  
  WHERE LastName LIKE 'sa%'
  
  Someone gives you a memo to bring up the details of a customer, but their terrible handwriting makes some letters illegible. Use this search:
  
  SELECT * FROM Customer
  
  WHERE LastName LIKE 'So_ra_o'
  
  Or narrow the search by what the letters might be:
  
  SELECT * FROM Customer
  
  WHERE LastName LIKE 'So[pyq]ra[nhm]o'