SQL Scalar Functions

1. UCASE() Function

The UCASE() function converts the value of selected field to uppercase.

Syntax:
SELECT UCASE(Column_name)
FROM table_name;

Example : Query using UCASE() Function.
Consider the following table titled as 'Stationary', which contains the information of products.

Write a query to convert values in 'Name' to uppercase, from table 'Stationary'.

IDNameQuantityPrice
1Pen10200
2Ink15300
3Notebook20400
4Pencil30150

SELECT UCASE (Name) AS NAME
FROM Stationary;

The result is shown in the following table.

NAME
PEN
INK
NOTEBOOK
PENCIL

2. LCASE() Function

The SQL LCASE() Function converts the value of selected field to lowercase.

Syntax:
SELECT LCASE(Column_name)
FROM table_name;

Example : Query using LCASE() Function.
Consider the following table titled as 'Stationary', which contains the information of products.

Write a query to convert values in 'Name' to lowercase, from table 'Stationary'.

IDNameQuantityPrice
1Pen10200
2Ink15300
3Notebook20400
4Pencil30150

SELECT LCASE(Name) AS NAME
FROM Stationary;

The result is shown in the following table.

IDNameQuantityPrice
1pen10200
2ink15300
3notebook20400
4pencil30150

3. MID() Function

The SQL MID() Function is used to extract characters from a selected field from table.

Syntax:
SELECT MID(Column_name, starting_point, total length) AS Some_name
FROM table_name;

Example : Query using MID() Function.
Consider the following table titled as 'Stationary', which contains the information of products.

Write a query to extract characters from column 'Name' to from table 'Stationary'. Where, starting point of a character is 2 and total length is 3.

IDNameQuantityPrice
1Pen10200
2Ink15300
3Notebook20400
4Pencil30150

SELECT MID(Name, 2, 3) AS NAME
FROM Stationary;

The result is shown in the following table.

NAME
en
nk
ote
enc

4. LEN() Functions

The SQL LEN() function returns the length of the characters of selected column (text field)

Syntax:
SELECT LEN(column_name)
FROM table_name;

Example : Query using LEN() Function.
Consider the following table titled as 'Stationary', which contains the information of products.

Write a query to extract length of characters of column 'Name' of table 'Stationary'.

IDNameQuantityPrice
1Pen10200
2Ink15300
3Notebook20400
4Pencil30150

SELECT LEN(Name) AS Length_of_NAME
FROM Stationary;

The result is shown in the following table.

NameLength_of_NAME
Pen3
Ink3
Notebook8
Pencil6