In the previous section we saw Aggregate Functions, in this section let us discuss Scalar Functions.
Scalar Function:
A scalar function is a type of function that returns a single value based on the input parameters. In SQL queries, scalar functions can manipulate data, perform calculations, and retrieve specific values from a database.
Following are a few of the most commonly used Aggregate Functions:
Function | Description |
LCASE() | Used to convert string column values to lowercase |
UCASE() | This function is used to convert a string column values to Uppercase. |
LEN() | Returns the length of the text values in the column. |
MID() | Extracts substrings in SQL from column values having String data type. |
ROUND() | Rounds off a numeric value to the nearest integer. |
NOW() | This function is used to return the current system date and time. |
FORMAT() | Used to format how a field must be displayed. |
Syntax & Examples of Scalar Functions:
- UCASE():
Syntax:
SELECT UCASE(ColumnName)
FROM TableName;
Consider a Student Table:
StudentID | StudentName | Marks |
1 | John | 64.12 |
2 | Sam | 72.50 |
3 | Alan | 45.90 |
4 | Rabecca | 86.37 |
5 | Anjali | 92.01 |
Query to retrieve the names of all students in uppercase.
SELECT UCASE(StudentName)
FROM Student;
Output:
StudentName |
JOHN |
SAM |
ALAN |
RABECCA |
ANJALI |
- LCASE():
Syntax:
SELECT LCASE(ColumnName)
FROM TableName;
Query to retrieve the names of all students in lowercase.
SELECT LCASE(StudentName)
FROM Student;
Output:
StudentName |
john |
sam |
alan |
rabecca |
anjali |
- LEN():
Syntax:
SELECT LENGTH(String) AS SampleColumn;
Query to extract the length of the student name “Rabecca”.
SELECT LENGTH(“Rabecca”) AS StudentNameLen;
Output:
StudentNameLen |
7 |
- MID():
Syntax:
SELECT MID(ColumnName, Start, Length)
FROM TableName;
Query to extract substrings from the StudentName column.
SELECT MID(StudentName, 1, 2)
FROM Students;
Output:
StudentName |
Jo |
Sa |
Al |
Ra |
An |
- ROUND():
Syntax:
SELECT ROUND(ColumnName, Decimals)
FROM TableName;
Query to round the marks to the integer value.
SELECT ROUND(Marks)
FROM Student;
Output:
Marks |
64 |
73 |
46 |
86 |
92 |
- NOW():
Syntax:
SELECT NOW();
Query to retrieve the current date and time.
SELECT NOW();
Output:
2023-04-03 04:29:36 |
Note: also read about SQL Functions-I
Follow Me
Please follow me to read my latest post on programming and technology if you like my post.
https://www.instagram.com/coderz.py/
https://www.facebook.com/coderz.py
Staying up to the mark is what defines me. Hi all! I’m Rabecca Fatima a keen learner, great enthusiast, ready to take new challenges as stepping stones towards flying colors.
Leave a Comment