# 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