Scalar Functions

There are seven scalar functions in SQL which return a single value which is based on the input value. These seven scalar functions are as follows:

1. FORMAT() formats the field to be returned.
2. LEN() returns the length of the text field.
3. ROUND() rounds a numeric value to the number of decimal places specified.
4. NOW() returns the current system date and time.
5. UCASE() returns a text field in upper case.
6. LCASE() returns a text field in lower case.
7. MID() returns part of a text field.

---

FORMAT()

The FORMAT() function converts the field to be returned.
Syntax:

SELECT FORMAT(column_name, format) FROM table_name;

Below is the table, marks_maria:

Subject	I term	II term	III term	IV term
Maths	90	78	75	80
English	72	68	76	62
French	78	75	63	58
Science	93	88	84	75

---

LEN()

The LEN() function returns the length of the text field.
Syntax:

SELECT LEN(column_name) FROM table_name;

To display the length of the values in the column, Subject, the query will be:

SELECT Subject, LEN(Subject) AS Length
FROM marks_maria;

Below is the output:

Subject	Length
Maths	5
English	7
French	6
Science	7

---

ROUND()

The ROUND() function rounds a numeric value to the number of decimal places specified.
Syntax:

SELECT ROUND(column_name, decimal_value) FROM table_name;

Have a look at the table, expense, below:

Expense	Amount($)
House	1300
Travel	500.56
Grocery	232.83
Recreation	126.54
Others	300

The query given below rounds off the Amount($) value to the nearest decimal.

SELECT Expense, Amount($), ROUND(Amount($), 0)
FROM expense
WHERE (Expense='Travel' OR Expense='Grocery');

The output will be:

Expense	Amount($)	ROUND(Amount($), 0)
Travel	500.56	501
Grocery	232.83	233

---

NOW()

The NOW() function returns the current system date and time.
Syntax:

SELECT NOW();

This simple syntax given above will return the current date and time on the system. The output of the above statement will be:

NOW()
2009-05-15 12:08:34

SELECT NOW() AS Current_time;

The above query will have the following result-set:

Current_time
2009-05-15 12:13:41

NOW() can be used along with any SELECT statement to display the current system date and time.

---

UCASE()

The UCASE() function returns a text field in upper case.
Syntax:

SELECT UCASE(column_name) FROM table_name;

Look at the query given below:

SELECT *, UCASE(Expense)
FROM expense;

The result-set will be:

Expense	Amount($)	UCASE(Expense)
House	1300	HOUSE
Travel	500.56	TRAVEL
Grocery	232.83	GROCERY
Recreation	126.54	RECREATION
Others	300	OTHERS

---

LCASE()

The LCASE() function returns a text field in lower case.
Syntax:

SELECT LCASE(column_name) FROM table_name;

The query given below will convert the text in the Expense column into lower case.

SELECT LCASE(Expense) AS expense FROM expense;

The output will be:

expense
house
travel
grocery
recreation
others

---

MID()

The MID() function returns a part of a text field.
Syntax:

SELECT MID(column_name, start_point, length) FROM table_name;

The start_point tells the position from where the text is to be displated. The length tells the length of the text to be displayed. If not specified, the entire text starting from the start_point will be displayed. Suppose, we want to display three characters starting from the second character. The query will be:

SELECT MID(Expense, 2, 3) AS sub_text
FROM expense;

The output will be:

sub_text
ous
rav
roc
ecr
the