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.

Syntax
    SELECT UCASE(column_name) FROM table_name;

Example:

Consider a table named “contacts”, having the following data.

Sample Data

MySQL Scalar Functions

The following SQL statement converts all the firstname to uppercase in the table contacts.

UCASE Example
    SELECT UCASE(firstname) FROM contacts;
Output

MySQL Scalar Functions

 

LCASE Function

The UCASE() function converts the value of a field to lowercase.

Syntax
     SELECT LCASE(column_name) FROM table_name;

 

Example:

The following SQL statement converts all the firstname back to lowercase in the table contacts.

LCASE Example
     SELECT LCASE(firstname) FROM contacts;
Output

MySQL Scalar Functions

 

MID Function

The MID function is used to extract substrings from column values of string type in a table.

Syntax
     SELECT MID(column_name, start, length) FROM table_name
ParameterDescription
column_nameRequired. The field to extract characters from
startRequired. Specifies the starting position
lengthOptional. 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

MID Example
     SELECT MID(firstname,1,3) FROM contacts;
Output

MySQL Scalar Functions

 

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.

Syntax
     SELECT ROUND(column_name, decimals) from table_name
ParameterDescription
column_nameRequired. The field to round.
decimalsRequired. Specifies the number of decimals to be returned.

 

Example:

Consider a table named “contacts”, having the following data.

Sample Data

MySQL Scalar Functions

 

The following SQL statement selects the salary and rounds the salary in one decimal places.

ROUND Example
     SELECT ROUND(salary, 1) FROM contacts;
Output

MySQL Scalar Functions

 

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.

NOW Example
    SELECT NOW();
Output

MySQL Scalar Functions

 

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.

 

Syntax
     SELECT FORMAT(column_name,decimal_places) FROM table_name;
ParameterDescription
column_nameRequired. The field to be formatted.
decimal_placesThe number of decimal places to round the number.

 

Example:

The following statement will return the formatted values of salary from contacts table.

FORMAT Example
     SELECT FORMAT(salary, 4) FROM contacts;
Output

MySQL Scalar Functions

Anwar Yakkiparamban

Anwar Yakkiparamban is the founder of Lauyou Learning. Prior to Lauyou learning, Anwar worked at ARD Engineering & Development, Qatar. He holds bachelor degree in Electronics and Communication Engineering from Govt. Engineering College Idukki.

You may also like...