Functions Flashcards
What is a function?
A function is an SQL Server object with a specified name and optional parameters that operates as a single logical unit.
What do you need to include to call a function?
Functions are called by providing the name of the function along with a set of parenthesis.
Example: function (arg1, arg2, arg3)
SELECT DATE_FORMAT(“2017-06-15”, “%Y”)
In the query above, what does the “DATE_FORMAT” represent?
The function
SELECT DATE_FORMAT(“2017-06-15”, “%Y”)
In the query above, what are the values inside the parenthesis called?
Arguments
DATE
The DATE() function extracts the date part from a datetime expression.
SELECT DATE(“2017-06-15”);
TIME
The TIME() function extracts the time part from a given time/datetime.
SELECT TIME(“19:30:10”);
DATETIME
The DATETIME type is used for values that contain both date and time parts.
TIMESTAMP
The TIMESTAMP data type is used for values that contain both date and time parts.
SELECT TIMESTAMP(“2017-07-23”, “13:10:11”);
YEAR
The YEAR() function returns the year part for a given date (a number from 1000 to 9999).
SELECT YEAR(“2017-06-15”);
What is the format used for date values?
YYYY-MM-DD
What is the format used for time values?
hh:mm:ss
SELECT DISTINCT
The SELECT DISTINCT statement is used to return only distinct (different) values. It will return only one value for each distinct result.
COUNT (Aggregate Function)
Counts how many rows are in a particular column.
SUM (Aggregate Function)
Adds together all the values in a particular column.
MIN (Aggregate Function)
Returns the lowest value in a particular column.
MAX (Aggregate Function)
Returns the highest value in a particular column.
AVG (Aggregate Function)
Calculates the average of a group of selected values.
What are the 5 Aggregate Functions?
Count, Sum, Min, Max, Avg
What is a string?
A string is a collection of characters that you cannot use in arithmetic calculation. The characters you store in these data types can be uppercase or lowercase letters, numerals, and special characters such as the “at” sign (@), ampersand (&), and exclamation point (!) in any combination. It could be a name, an email address, a telephone number.
What case conversion function can you use to convert text to uppercase?
UPPER, UCASE
What case conversion function can you use to convert text to uppercase?
LOWER, LCASE
REPLACE
Is used to replace a string or part of a string with another string.
Example:
You want to change ‘USA’ for ‘United States’ in the country column of a database.
SELECT REPLACE( country, ‘USA’, ‘United States’ )
FROM customers
FORMAT
Used to format date/time values and number values.
FORMAT(value, format)
For number values - FORMAT(value, # of decimals)
Example: FORMAT(2034.567, 2)
For dates - FORMAT(value, date format preferred)
Example: FORMAT(getdate(), ‘dd-MM-yy’)
ROUND
Rounds a number to a specified number of decimal places.
ROUND(value, # of decimals)
Example:
ROUND(2034.567, 2)
It will return 2034.57, the number rounded to only 2 decimals.
TRIM
Removes the space character OR other specified characters from the start or end of a string.
SUBSTR
Extracts a substring from a string (starting at any position)
SUBSTR(string, start, length)
What is a leading space?
A space in front of the value input in the field
What is a trailing space?
A space left after the value input in the field
What function should you use to remove a leading space from a value?
ltrim
What function should you use to remove a trailing space from a value?
rtrim
CONCAT
Adds two or more strings together.
CONCAT(string1, string2, …., string_n) [Within the paranthesis you include all the string you want to join together]
CONCAT_WS
The CONCAT_WS() function adds two or more strings together with a separator.
CONCAT_WS(separator, string1, string2, …., string_n)
Here, the first argument is whatever you want to use as a separator between each string, followed by all the strings you want to join together.
