How to MySQL- Scalar functions
This SQL Tutorial provides you a summary of some of the most common MySQL Scalar Functions. Scalar functions act only on single data values. MySQL provides many scalar functions that include.
- UCASE() – Converts a field to upper case
- LCASE() – Converts a field to lower case
- MID() – Extract characters from a text field
- ROUND() – Rounds a numeric field to the number of decimals specified
- NOW() – Returns the current system date and time
- FORMAT() – Formats how a field is to be displayed
UCASE Function
The UCASE() function converts the value of a field to uppercase.
|
1 2 3 4 5 |
SELECT UCASE(column_name) FROM table_name; |
Example:
Consider a table named “contacts”, having the following data.
The following SQL statement converts all the firstname to uppercase in the table contacts.
|
1 2 3 4 5 |
SELECT UCASE(firstname) FROM contacts; |
LCASE Function
The UCASE() function converts the value of a field to lowercase.
|
1 2 3 4 5 |
SELECT LCASE(column_name) FROM table_name; |
Example:
The following SQL statement converts all the firstname back to lowercase in the table contacts.
|
1 2 3 4 5 |
SELECT LCASE(firstname) FROM contacts; |
MID Function
The MID function is used to extract substrings from column values of string type in a table.
|
1 2 3 4 5 |
SELECT MID(column_name, start, length) FROM table_name |
| Parameter | Description |
|---|---|
| column_name | Required. The field to extract characters from |
| start | Required. Specifies the starting position |
| length | Optional. The number of characters to return. If omitted, the MID() function returns the rest of the text |
Example:
The following SQL statement selects the first Three characters from the firstname column from the contacts table
|
1 2 3 4 5 |
SELECT MID(firstname,1,3) FROM contacts; |
ROUND Function
ROUND function is used to round a numeric field to a number of the nearest integer. It is used on Decimal point values.
|
1 2 3 4 5 |
SELECT ROUND(column_name, decimals) from table_name |
| Parameter | Description |
|---|---|
| column_name | Required. The field to round. |
| decimals | Required. Specifies the number of decimals to be returned. |
Example:
Consider a table named “contacts”, having the following data.
The following SQL statement selects the salary and rounds the salary in one decimal places.
|
1 2 3 4 5 |
SELECT ROUND(salary, 1) FROM contacts; |
NOW Function
The MySQL NOW() function returns the current date and time in the configured time zone as a string or a number in the ‘YYYY-MM-DD HH:MM:DD’ or ‘YYYYMMDDHHMMSS.uuuuuu’ format.
The following statement will return the current date and time in ‘YYYY-MM-DD HH:SS:MM’ format.
|
1 2 3 4 5 |
SELECT NOW(); |
FORMAT Function
The MySQL FORMAT function formats a number as a format of ‘#,###.##’, rounding it to a certain number of decimal places and then it returns the result as a string.
|
1 2 3 4 5 |
SELECT FORMAT(column_name,decimal_places) FROM table_name; |
| Parameter | Description |
|---|---|
| column_name | Required. The field to be formatted. |
| decimal_places | The number of decimal places to round the number. |
Example:
The following statement will return the formatted values of salary from contacts table.
|
1 2 3 4 5 |
SELECT FORMAT(salary, 4) FROM contacts; |
