SQL Functions-II

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