Scalar Functions in SQL
FREE
Overview
Apart from storing and accessing data in databases, multiple operations can be performed on the data to gain useful insights like finding any pattern in the dataset, the relation between different variables, etc. In-built functions can be leveraged here to carry out different operations such as rounding off any value or finding any subset etc.
In this article, we are going to learn about Scalar Functions in SQL.
What are Scalar Functions in SQL?
Scalar Functions in SQL work on each argument provided and return a single value output. For example, if we need to change the case of the uppercase letter, it would be written as :
Syntax:
Output:
Explaination:
Any number of arguments can be given in the functions but it gives individual results for each argument.
Some of the most used Scalar Functions in SQL are:
- UCASE()
- LCASE()
- MID()
- LENGTH()
- ROUND()
- NOW()
- FORMAT()
1. UCASE()
The UCASE() or the upper case function changes the case of the string to uppercase. An individual string or a column can be passed as the parameter to the function.
Syntax:
OR
Example:
Output:
Using UCASE(), the case of the string has been changed to uppercase.
tree -> TREE
2. LCASE()
The LCASE() or the lower case function changes the case of the string to lowercase. Like UCASE(), any number of strings can be passed as a parameter to the LCASE() function.
Syntax:
OR
Example:
Output:
Explaination: Here, the case of the letters is changed to lowercase using the LCASE() function.
HeLLOWorld -> helloworld
3. MID()
The MID() function is used to extract substrings from the string passed as a parameter or from the table's column, which is of string datatype.
Syntax:
OR
Where,
string/column_name is full of the string from which the substrings need to be extracted.
start refers to the integer value that denotes the start position, starting from 1.
length is the integer value that denotes the length of the substring to be extracted from the main string starting from start. Length is an optional parameter. If not stated, the whole string after the start position will be extracted as result.
Example:
Output:
Explaiation: In the above example, a substring has been extracted from the string.
start = 11, means the extraction would start from 11th position,i.e., W length = 6, 6 letters would be extracted out.
As a result, 'Wonder' is returned.
4. LENGTH()
The LENGTH() function returns the length of the string passed as a parameter.
Syntax:
OR
Example:
Output:
using LENGTH(), the length of the string provided is returned. Here, the string is 'sunshine', 8 letter word. Thereby, 8 is returned.
5. ROUND()
The ROUND() function is used to round off the numeric values to the specified decimal value.
Syntax:
OR
Where, Decimal is the number of decimals that needs to be fetched.
Example:
Output:
The ROUND() function has rounded off the value given to 3 decimal places.
3245.8762 -> 3245.876
6. NOW()
The NOW() function is used to get the current system's date and time.
Syntax:
OR
Example:
Output:
Here, the NOW() function has simply returned the current date and time.
7. FORMAT()
The FORMAT() function is used to modify the display of the column values. There can be different formats that we can use.
Syntax:
OR
Where, FormatRequired is the format in which the value/string will be displayed.
Example:
Output:
FORMAT() function has returned the value rounded off 2 decimal places after dividing by 3.
Conclusion
- Scalar functions return output for each row of a column.
- They are very handy when the format of the text/string needs to be modified as scalar functions work on the display of string.
- The syntax of every function is intuitive and easy to use.