Scalar Functions - SQL Query

Scalar Functions

Scalar functions are used to return single value that is based on input value.

Consider the following table 'Students'

Stud_ID	Name	Phone	City	Country
1	Alex	654124	Perth	Australia
2	Martin	654125	Sydney	Australia
3	Shruti	910001	Delhi	India
4	Jaya	910002	Mumbai	India
5	Paul	450525	London	England
6	Andrew	450526	London	England

i) Display the column 'Name' in uppercase from table 'Students'.

SELECT UCASE(Name) AS Student_Name FROM Students

The UCASE() function converts a particular field into uppercase.

Output:

Student_Name
ALEX
MARTIN
SHRUTI
JAYA
PAUL
ANDREW

ii) Display the column 'Name' in lowercase from table 'Students'.

SELECT LCASE(Name) AS Student_Name FROM Students

The Function LCASE() converts a selected field into lowercase.

Output:

Student_Name
alex
martin
shruti
saya
paul
andrew

iii) Display the first three characters of Column 'City', from table 'Students'.

SELECT MID(City,1,3) AS City3 FROM Students

The function MID() is used to extract the characters from a text field.

Output:

City3
Per
Syd
Del
Mum
Lon
Lon

iv) Display the total length value of field 'City' from table Students.

SELECT Name, LEN(City) AS Length_City FROM Students

The LEN() function computes the total length of the characters present in the selected field. Here the Name is selected to display the name of student and LEN(City) will return the total length of city in all rows.

Output:

Name	Length_City
Alex	5
Martin	6
Shruti	5
Jaya	6
Paul	6
Andrew	6