In the last tutorial we left off with the SUM(column) function. In this article we will cover the remaining Aggregate Functions and the Scalar Functions. We might even make some fat jokes, some mama jokes, and as always, some nerd jokes.
Scalar functions work against one value and return one value based on the input. Aggregate functions work on a bunch of data and return one result. I understand if you fell asleep during that; I did just writing it.
Here is a table of scalar functions.
Function Name
What it does
UCASE(c)
Changes a field to Uppercase
LCASE(c)
Changes a field to lowercase
MID(c,start[,end])
Extracts characters from a text field
LEN(c)
Returns the length of a text field
INSTR(c,char)
Returns the position of a character in a field
LEFT(c,number_of_char)
Returns the left part of text
RIGHT(c,number_of_char)
Returns the right part of text
ROUND(c,decimals)
Rounds a numeric field to a number of decimals
MOD(x,y)
Gives the remainder in division
NOW()
Returns the present system date
FORMAT(c,format)
Formats a field
DATEDIFF(d,date1,date2)
For date calculation
UCASE(c)
If you want make all the characters of a selected item uppercase, you can use the UCASE function, like so:
Select UCASE('darth ladel') from Employees;
This will return the following: DARTH LADEL
LCASE(c)
Likewise, you may also wish to force all of the characters to be lowercase.
