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.
The Mid function extracts a certain number of characters from a text field. There are three parts to the Mid function:
String: The string from which you wish to extract data. If the string is I Hate You and you want the word You, you could start with the word HATE for your string.
Start: Where you want the start of the extraction to begin. For instance, if you wanted the word YOU from the string I Hate You, you could put your string as Hate and your start as 6 (the space between hate and you counts as one)
Length: How many characters to return. If you wanted the word YOU, this would be 3.
Select Mid([Luke Breadslicer],6,5) as SampleColumn From Employees;
The above code would create a column named SampleColumn and display the word: Bread.
LEN(c)
The Len function counts the length of the string you specify.
Select Len([Luke Breadslicer]) as SampleColumn From Employees;
The above would create a column named SampleColumn and display the count of the characters in the string Luke Breadslicer, which in this case would be 16 (including the space).
Left(c,number_of_char)
The Left function returns a string, starting from the left and working its way to the right, of however many places you tell it. I will use the string Luke Breadslicer in the example below:
Select Left([Luke Breadslicer], 4) as SampleColumn;
Again, this creates a column named SampleColumn, and displays the string Luke.
Right(c,number_of_char)
As you can probably guess, the Right function works the in same way as the Left function, only from the opposite end. If you specify a number larger than the string, it returns the entire string.
Select Right([Luke], 3) as SampleColumn from Employees;
This would return a column named SampleColumn and display the data: Uke
