What are the Scalar Functions in SQL? Complete Guide

In this article we will see the Scalar Functions in SQL with practical examples.

Scalar Functions

Scalar functions always return a single value using the given input value. Following are some of commonly used scalar functions.

It is also one of the important interview question. Your interviewer may ask you what are the Scalar Functions in SQL?

Let us see in detail.

Below are the list of SQL scalar functions.

  1. UCASE – Converts a given field to upper case.
  2. LCASE – Converts a given field to lower case.
  3. MID – Extract characters from a given text field.
  4. LEN – Returns the length of a given text field.
  5. ROUND – Rounds a given numeric field to the number of decimals specified.
  6. NOW – Returns the current system date and time.
  7. FORMAT – Formats the field based on the requirement.

UCASE Function

This function is used to convert given table column field to upper case.

Syntax for this function is as follows

SELECT UCASE(column_name) 
FROM table_name

Practical Example Of using UCASE Function

Consider the following employee table.

SQL query for this function is as follows.

SELECT UCASE(Name)

FROM Employee;

Results for this query will be as follows

LCASE() Function

LCASE function is used to convert specific table column values to lower case.

Syntax for this function is as follows

SELECT LCASE(column_name) 
FROM table_name;

Practical Use Of LCASE Function

Consider the following Employee Table

Let us write a query to convert this string values to lower case

SELECT LCASE(Name) FROM Employee;

Results for this query will be as follows

MID() Function

MID function is used to capture the specific string values within the table column values

Syntax for this function will be as follows

SELECT MID(column_name, start, length)
 FROM table_name

Practical Use Of MID Function

Consider the following Employee table

Here we can write the SQL query as follows

SELECT MID(Name, 2, 2) FROM Employee

The result set for this query will be as follows

ROUND Function

Round function is used to covert nearest decimal values for a specific table column values

Syntax for this ROUND function will be

SELECT ROUND(column_name, decimals) 
FROM  table_name;

Practical Use Of Round Function

Let us consider the following employee table for this function

Result set for this query will be as follows

NOW() Function

Now function is isued to display the current system date and time.

Syntax: Select Now() from table_name

Consider the below table.

Let us see the sample query.

Select Product Name, Location,
NOW() as SL Date from Products

ROUND() Function

Round function is used rownd the number to the specific decimal point.

Syntax: ROUND(Number, Decimal, Operation)

Let us see the query for this ROUND function.

Select ROUND(543.2316, 2) 
as Round Value

Results for this query will be

Would you like to know more. You can read this article SQL Server Round Function.

I hope this article is useful to you. Please leave your comment in the comment section below.

Here you can read the related Interview question SQL Window Functions

1 thought on “What are the Scalar Functions in SQL? Complete Guide”

Comments are closed.