coderz.py

Keep Coding Keep Cheering!

SQL Functions-II

SQL Views

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:

FunctionDescription
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:

StudentIDStudentNameMarks
1John64.12
2Sam72.50
3Alan45.90
4Rabecca86.37
5Anjali92.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

Leave a Comment

Your email address will not be published. Required fields are marked *

Advertisement