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.
- UCASE – Converts a given field to upper case.
- LCASE – Converts a given field to lower case.
- MID – Extract characters from a given text field.
- LEN – Returns the length of a given text field.
- ROUND – Rounds a given numeric field to the number of decimals specified.
- NOW – Returns the current system date and time.
- 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
You have noted very interesting details ! ps nice site.