Scalar Functions in SQL

We can perform different operations on the data stored in the SQL database. This can be done with the help of built-in functions provided by SQL. Scalar functions are the built-in functions in SQL, and whatever be the input provided to the scalar functions, the output returned by these functions will always be a single value.

In SQL, each record is operated independently by the scalar functions.

Some of the commonly used scalar functions in SQL includes:

1. UCASE()
2. LCASE()
3. MID()
4. LENGTH()
5. ROUND()
6. NOW()
7. FORMAT()

Let us see each of them in more detail with the help of examples. We will use the MySQL database for writing all the queries.

Consider we have customers table with the following records:

1. UCASE()

• The UCASE() or upper case function will change the case of the string to upper case characters.
• Here, the string can also be passed as a parameter or the table's column name, which contains the string, can also be passed as a parameter to the UCASE () function.

Syntax to convert a string in uppercase:

OR

Syntax to convert a string in uppercase, which is stored in a table's column:

SELECT UCASE (ColumnName) FROM TableName;

Example 1:

Here, the UCASE () function with the string 'Welcome to Javatpoint' as a parameter is used in a SELECT query to print the string in the upper case. 'Upper_Case_String' is an alias.

You will get the following output:

'WELCOME TO JAVATPOINT' is the upper case string for the parameter passed to the UCASE () function. Hence, it is displayed as an output.

Example 2:

Here, the 'Address' column is passed as a parameter to the UCASE () function in a SELECT query to print the values of the address column in the upper case. 'Upper_Case_String' is an alias.

You will get the following output:

All the column values present in ID, Name, Age and salary are displayed as it is. The column values present in the address column are displayed in the upper case.

2. LCASE()

• The lower case function will change the case of the string to lower case characters.
• Here, the string can also be passed as a parameter or the table's column name, which contains the string, can also be passed as a parameter to the LCASE () function.

Syntax to convert a string in lowercase:

OR

Syntax to convert a string in lowercase which is stored in a table's column:

Example 1:

Here, the LCASE () function with the string 'WELCOME TO JAVATPOINT' as a parameter is used in a SELECT query to print the string in lower case. 'LowerCase_String' is an alias.

You will get the following output:

'welcome to javatpoint' is the lower case string for the parameter passed to the LCASE () function. Hence, it is displayed as an output.

Example 2:

Here, the 'Name' column is passed as a parameter to the LCASE () function in a SELECT query to print the values of the 'Name' column in lower case. 'NameLowerCase' is an alias.

You will get the following output:

All the column values present in ID, Age, Address and salary are displayed as it is. The column values present in the 'Name' column are displayed in lower case.

3. MID()

• MID () function is used to extract substrings from the table's column, which contain values of string type.

Syntax to extract a substring:

where,

String: contains the entire string from which we want to extract a specific portion of the string.

Starting index: The integer value present in the starting index decides where the characters in the string are to be extracted from the beginning or end of the string.

Length: The integer value present in the length field tells the count of characters to be extracted from the string. If the length is not specified, then the entire string will be extracted.

Note: Length is an optional parameter

OR

Syntax to extract a substring which is stored in a table's column:

Example 1:

Here, the MID () function with the three parameters: 'Welcome to the world of databases', 16, 18 is passed as a parameter to the SELECT query to print the substring starting with the index 16 and contains 18 characters. 'Substring' is an alias.

You will get the following output:

'World of databases' is the substring for the parameter passed to the MID () function. Hence, it is displayed as an output.

Example 2:

Here, the MID () function with the three parameters: Name, 3, 5 is passed as a parameter to the SELECT query to print the substring starting with the index 3 and contains 5 characters. ' NameSubstring ' is an alias.

You will get the following output:

All the column values present in ID, Age, Address and salary are displayed as it is. Only the substrings starting with the index 3 having 5 characters are displayed in the' Name' column.

4. LENGTH()

• LENGTH () function returns the length of the string in the column.

Syntax to find the length of a string:

OR

Syntax to find the length of a string from the table's column:

Example 1:

mysql> SELECT LENGTH ("Welcome to the world of databases") AS LengthOfString;

Here, the LENGTH () function with the string 'Welcome to the world of databases' as a parameter is used in a SELECT query to print the length of the string. 'LengthOfString' is an alias.

You will get the following output:

'33' is the length of the string for the parameter passed to the LENGTH () function. Hence, it is displayed as an output.

Example 2:

Here, the 'Name' column is passed as a parameter to the LENGTH () function in a SELECT query to print the length of the values of the 'Name' column. 'LengthOfName' is an alias.

You will get the following output:

All the column values present in ID, Age, Address and salary are displayed as it is. In the 'Name' column, only the length of the string is displayed.

5. ROUND()

The ROUND () function is used to round a numeric column to the number of decimals specified.

Syntax to round the numeric values:

where,

Decimal represents the number of decimals to be fetched.

OR

Syntax to round the numeric values from the table's column:

Example 1:

Here, the ROUND () function with two different parameters containing numeric values 18000.44444, 2 is used in a SELECT query to print the rounded value for the given number. Since 2 is passed as a second parameter, the number passed in the first parameter will be rounded to two decimal points. ' RoundedValue ' is an alias.

You will get the following output:

'18000.44' is the rounded value for the parameter passed to the ROUND () function. Hence, it is displayed as an output.

Example 2:

Here, the ROUND () function with two different parameters containing numeric values 18000.44444, 0 is used in a SELECT query to print the rounded value for the given number. Since 0 is passed as a second parameter, the number passed in the first parameter will be rounded without any decimal points. ' RoundedValue ' is an alias.

You will get the following output:

'18000' is the rounded value for the parameter passed to the ROUND () function. Hence, it is displayed as an output.

Example 3:

mysql> SELECT ROUND (18000.44444, 3) AS RoundedValue;

Here, the ROUND () function with two different parameters containing numeric values 18000.44444, 3 is used in a SELECT query to print the rounded value for the given number. Since 3 is passed as a second parameter, the number passed in the first parameter will be rounded to three decimal points. ' RoundedValue ' is an alias.

You will get the following output:

'18000.444' is the rounded value for the parameter passed to the ROUND () function. Hence, it is displayed as an output.

6. NOW()

• NOW () function returns the current system' date and time.

Syntax to find the current date and time:

OR

Syntax to find the current date and time from a table's column:

Example 1:

The NOW () function is used in a SELECT query to print the current system date and time. 'CurrentDatenTime' is an alias.

You will get the following output:

'2021-09-13 10:38:24' is the current date and time of the system. Hence, it is displayed as an output.

Example 2:

Here, the NOW() function is used in a SELECT query to print the current system's date and time in a table's column. 'DatenTime' is an alias.

You will get the following output:

All the column values present in ID, Name, Age, Address and salary are displayed as it is. In the 'DatenTime' column, the current system date and time is displayed.

7. FORMAT()

• The FORMAT () function is used to format how a column is to be displayed.

Syntax to format a value:

OR

Syntax to format a value present in table's column:

SELECT FORMAT (ColumnName, Decimal) FROM TableName;

Example 1:

The FORMAT () function with two different parameters containing numeric values 6789.6789, 2 is used in a SELECT query to print the formatted value after rounding off the given number. Since 2 is passed as a second parameter, the number passed in the first parameter will be rounded to two decimal points. 'Formatted_Number' is an alias.

You will get the following output:

'6,789.68' is the formatted value for the parameter passed to the FORMAT() function. Hence, it is displayed as an output.

Example 2:

The ROUND () function with two different parameters containing the column 'Salary' / 0.5, 2 is used in a SELECT query to print the formatted value. This formatted value will be obtained after the value contained in the 'Salary' column is divided by 2. Further, since 2 is passed as a second parameter, the value obtained after dividing the salary by 0.5 will be rounded to two decimal points. 'FormattedSalary' is an alias.

You will get the following output:

All the column values present in ID, Name, Age and Address are displayed as it is. In the 'FormattedSalary' column, the formatted salary values are displayed.