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:
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 |
