MySQL Functions with Examples

Contents show

MySQL Functions with Examples (Strings, Numeric and Date and Time)

MySQL Functions with examples
MySQL Functions with examples

Learn MySQL Functions


A function is a special type of predefined command set that performs some operation and returns a single value. Functions operate on zero, one, two or more values that are provided to them. The values that provided to functions are called parameter or arguments.


The MySQL functions have been categorized into various categories, such as String function, Mathematical functions, Date and Time functions so on. Although MySQL offers a big bouquet of functions, yet in this discussion we shall remain limited to some common functions.

1. MySQL String Functions


The string functions of MySQL can manipulate the text string in many ways. Some commonly used string functions are being discussed below.

more examples with answers are at bottom section.

FunctionDescriptionExamples
1.CHAR( )Returns the character for each integer passed1. SELECT CHAR(70, 65, 67, 69) ;
2. SELECT CHAR(65, 67.3, 69.3) ;
2.CONCAT( )Return concatenated stringSELECT CONCAT(name, aggregate) AS “Name Marks”
FROM student WHERE AGE = 14 OR age =16 ;
3.LOWER( ) / LCASE( )Returns the argument in lowercaseSELECT LOWER(‘MR.OBAMA’) AS “LOWERNAME1”,
LOWER(‘Ms.Gandhi’) AS “LOWERNAME2” ;
4.SUBSTRING( ),
SUBSTR( )
Returns the substring as specifiedA SELECT SUBSTR(‘ABCDEFG’, 3, 4) “SUBS” ;
B SELECT SUBSTR(‘ABCDEFGH, -5, 4 “SUBS”
5.UPPER( ) / UCASE( )Converts to uppercase SELECT UPPER(‘Large’) “Uppercase” ;
or SELECT UCASE(‘Large’) “Uppercase” ;
6.LTRIM( )Removes leading spacesSELECT LTRIM(‘ RDBMS MySQL’) ;
7.RTRIM( )Removes trailing spacesSELECT RTRIM(‘ RDBMS MySQL ‘) ;
8.TRIM( )Removes leading and trailing spacesSELECT TRIM( ‘ Bar One ‘) ;
9.INSTR( )Returns the index of the first occurrence of substringSELECT INSTR(‘ CORPORATE FLOOR’, ‘OR’) AS Instring ;
10.LENGTH( )Returns the length of a string in bytesSELECT LENGTH(‘CANDIDE’) “Length in characters” ;
11.LEFT( )Returns the leftmost number of characters as specifiedSELECT LEFT(‘USS/23/67/09’, 2) ;
12.RIGHT( )Returns the specified right-most number of charactersSELECT RIGHT(‘USS/23/67/09’, 2) ;
13.MID( )Returns a substring starting from the specified positionSELECT SUBSTRING(‘Quadratically’, 5, 6) ;
or SELECT MID(‘Quadratically’, 5, 6) ;
MySQL String Functions

MySQL Strings Functions Example

Example 1. Write a query to create a string from the ASCII values 70, 65, 67, 69.


Solution.

mysql> SELECT CHAR( 70, 65, 67, 69) ;
char (70, 65, 67, 69)
FACE
Output

1 row in set (0.00 sec)

Example 2. Concatenate name and aggregate for students having age as 14 or 16.
(Consider the table given below)

nameagesexmatricaggregate
Abu Bakar15M9531185456
Anya16F9531186340
Gurvinder14F9531187480
Ali16M9531188260
Michelle15F9531188321
Zubin15M9531184412
Simran15F9531183378
Fatimah14F9531182400
Anup15M9531181302
Mita16F9531180150
Table : Student


Solution.

mysql> SELECT CONCAT(name, aggregate) AS "Name Marks" FROM student
    -> WHERE age = 14 OR age = 16 ;
Name Marks
Aanya 340
Gurvinder 480
Ali 260
Fatimah 400
Mital 50
Output

5 row in set (0.00 sec)

Example 3. Display name ‘MR. OBAMA’ and ‘MS. Gandhi’ into lowercase. (MySQL Functions Examples)


solution.

mysql> SELECT LOWER('MR.OBAMA') AS "LOWERNAME1",
         -> LOWER('Ms.Gandhi') AS "LOWERNAME2" ;


Or

mysql> SELECT LCASE('MR.OBAMA') AS "LOWERNAME1",
    -> LCASE('Ms.Gandhi') AS "LOWERNAME2" ;
LowerName1LowerName2
mr. obamams. gandhi
output

1 row in set (0.00 sec)

Example 4. Display names of all employees in lowercase.
(consider table Empl given below)

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
8369SMITHCLERK89021990-12-18800.00NULL20
8499ANYASALESMAN86981991-02-201600.00300.0030
8521SETHSALESMAN86981991-02-221250.00500.0030
8566MAHADEVANMANAGER88391991-04-022985.00NULL20
8654MOMINSALESMAN86981991-09-281250.001400.0030
8698BINAMANAGER88391991-05-012850.00NULL30
8839AMIRPRESIDENTNULL1991-11-185000.00NULL10
8844KULDEEPSALESMAN86981991-09-081500.000.0030
8882SHIAVNSHMANAGER88391991-06-092450.00NULL10
8886ANOOPCLERK88881993-01-121100.00NULL20
8888SCOTTANALYST85661992-12-093000.00NULL20
8900JATINCLERK86981991-12-03950.00NULL30
8902FAKIRANALYST85661991-12-031300.00NULL20
8934MITACLERK88821992-01-231300.00NULL10
database table empl


Solution.


mysql> SELECT LOWER(ENAME) "employeeName"
    -> FROM emp1 ;
Or
mysql> SELECT LCASE(ENAME) "employeeName"
    -> FROM emp1 ;
employeeName
Smith
Anya
Seth
Mahadevan
Momin
Bina
Shiavansh
Scott
Amir
Kuldeep
Anoop
Jatin
Fakir
Mita
Output

14 row in set (0.00 sec)

Example 5. Display 4 characters extracted from 3rd left character onwards from string ‘ABCDEFGH’.


Solution.

mysql> SELECT SUBSTR('ABCDEFG', 3, 4) "SUBS" ;
subs
CDEF
Output

Example 6. Display first three character extracted from jobs of employees 8888 and 89000.

(Considered the table of example 4)


Solution.

mysql> SELECT SUBSTR(job, 1, 3)
   -> FROM emp1
   -> WHERE empno = 8888 OR empno = 8900 ;
SUBSTR(job, 1, 3)
ANA
CLE

Example 7. Write a query to remove leading and trailing spaces from string ‘ Bar One ‘.


Solution.

mysql> SELECT TRIM( '   Bar One    ') ;
TRIM( ‘ Bar One ‘)
Bar One
Output

1 row in set (0.00 sec)

Example 8. Display the position of occurrence of string ‘OR’ in string ‘CORPORATE FLOOR’ .


Solution.

mysql> SELECT INSTR(' CORPORATE FLOOR', 'OR') AS Instring ;
Instring
2
Output

1 row in set (0.00 sec)

Example 9. Display the position of string ‘LE’ in field job of table Empl.

(Considered the table of example 4)


Solution.

mysql> SELECT job, INSTR(job, 'LE') AS "LE in job" FROM empl ;
jobLE in job
CLERK2
SALESMAN3
SALESMAN3
MANAGER0
SALESMAN3
MANAGER0
MANAGER0
ANALYST0
PRESIDENT0
SALESMAN3
CLERK2
CLERK2
ANALYST0
CLERK2
Ouptut

14 row in set (0.00 sec)

Example 10. How many character are there in string ‘CANDIDE’.?


Solution.

mysql> SELECT LENGTH('CANDIDE') "Length in characters" ;
Length in characters
7
Output

Example 11. Display the employee names and the name-lengths for employee 8888 and 8900.

(Considered the table of example 4)


Solution.

mysql> SELECT ename, LENGTH(ename) AS "Name Length" FROM emp1
    -> WHERE empno = 8888 OR empno = 8900 ;
enameName Length
SCOTT
JATIN
5
5
Output

2 row in set (0.00 sec)

Example 12. Write a query to extract institute code from a string ‘USS/23/67/09’. The first three characters tell the institute code.


Solution.

mysql> SELECT LEFT('USS/23/67/09', 3) ;
SELECT LEFT(‘USS/23/67/09’, 3)
USS
Output

1 row in set (0.00 sec)

Example 13. Write a query to extract a sub string from string ‘Quadratically’ which should be 6 characters long and start from 5th character given string.


Solution.

mysql> SELECT SUBSTRING('Quadratically', 5, 6) ;
or
mysql> SELECT MID('Quadratically', 5, 6) ;
SUBSTRING(‘Quadratically’, 5, 6)
ratica
Output

1 row in set (0.00 sec)

2. Numeric Functions


The number functions are those functions that accept numeric values and after performing the required operation, return numeric values.
Some useful numeric functions are being discussed below :

more examples with answers are at bottom section.

FunctionDescriptionExample
1.MOD( )Returns the remainder of one expression by dividing by another expressionSELECT MOD(11, 4) “Modulus” ;
2.POWER( ) / POW( )Returns the value of one expression raised to the power of another expressionSELECT POWER(3,2) “Raised” ;
3.ROUND( )Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal pointsSELECT ROUND(15.193, 1) “Round” ;
4.SIGN( )This function returns sign of a given numberSELECT SIGN(-15) “Sign” ;
5.SQRT( )Returns the non-negative square root of numeric expression.SELECT SQRT(25) “Square root” ;
6.TRUNCATE( )Returns numeric exp1 truncated to exp2 decimal places. If exp2 is 0, then the result will have no decimal point.SELECT TRUNCATE(15.79, 1) “Truncate” ;
Numeric Functions

MySQL Numeric Functions Examples

Example 1. Find out the remainder of 11 divided by 4 and display 32.


solution.

mysql> SELECT MOD(11, 4) "Modulus", SELECT POWER(3,2) "Raised" ;
ModulusRaised
39
Output

1 row in set (0.00 sec)

Example 2. Truncate value 15.79 to 1 decimal place.


Solution.

mysql> SELECT TRUNCATE(15.79, 1) "Truncate" ;
Truncate
15.7
Output

3. Date and Time Functions


Date functions operate on values of the DATE datatype.

more MySQL Functions examples with answers are at bottom section.

FunctionDescriptionExample
1.CURDATE( ) / CURRENT_DATE( ) / CURRENT_DATEReturns the current dateSELECT CURDATE( ) ;
2.DATE( )Extracts the date part of a date or date-time expressionSELECT DATE(‘2021-02-28 01:02:03’) ;
3.MONTH( )Returns the month from the date passedSELECT MONTH(‘2020-02-03’) ;
4.YEAR( )Returns the yearSELECT YEAR(‘2020-02-03’) ;
5.DAYNAME( )Returns the name of the weekdaySELECT DAYNAME(‘2020-02-03’) ;
6.DAYOFMONTH( )Returns the day of the month (1-31)SELECT DAYOFMONTH(‘2020-02-03’) ;
7.DAYOFWEEK( )Returns the weekday index of the argumentSELECT DAYOFWEEK(‘2020-02-03’) ;
8.DAYOFYEAR( )Return the day of the year (1-366)SELECT DAYOFYEAR(‘2020-02-03’) ;
9.NOW( )Returns the time at which the function executesSELECT NOW( ) ;
10.SYSDATE( )Returns the current date and timeSELECT NOW( ), SLEEP(2), NOW( ) ;
Date and Time Functions

Example 16. Write a query to display current date on your system.


Solution.

mysql> SELECT CURDATE( ) ;
CURDATE( )
2021-02-28
Output

1 row in set (0.00 sec)

Example 1. Write a query to display date after 10 days of current date on your system.


Solution.

mysql> SELECT CURDATE( ) + 10 ;
CURDATE( ) + 10
20210238
Output

1 row in set (0.00 sec)

Example 2. Write a query to extract date from a given datetime value ‘2021-02-28 1-02-28 01:02:03’.


Solution.

mysql> SELECT DATE('2021-02-28 01:02:03') ;
DATE(‘2021-02-28 01:02:03’)
2021-02-28
Output

1 row in set (0.00 sec)

Example 3. Write a query to extract month part from date 3rd Feb 2021.


Solution.

mysql> SELECT MONTH('2021-02-03') ;
MONTH(‘2021-02-03’)
2
Output

1 row in set (0.00 sec)

Example 4. Write a query to display current date and time.


Solution.

mysql> SELECT NOW( ) ;
NOW( )
2021-02-28 15:27:20
Output

1 row in set (0.00 sec)

Example 5. Write a query to illustrate the difference between now( ) and sysdate( ).


Solution.

mysql> SELECT NOW( ), SLEEP(2), NOW( ) ;
NOW( )SLEEP(2)SYSDATE( )
2021-02-28 15:32:3402021-02-28 15:32:34
Output

1 row in set (2.01 sec)

mysql> SELECT SYSDATE( ), SLEEP(2), SYSDATE( ) ;
SYSDATE( )SLEEP(2)SYSDATE( )
2021-02-28 15:33:1502021-02-28 15:33:17
Output

1 row in set (2.01 sec)

Compare the above two results. THe now( ) function will return the same result even if you use it multiple times in same statement, even after creating delays (such as through sleep( ) that creates delay of specified seconds) because the now( ) function returns the begin-time of statement, whereas sysdate( ) function returns the execution time of its own i.e., at what time it started to execute. After reading these lines, compare the above results once again. Now you will clearly understand the difference between the functionality of the two functions now( ) and sysdate( ).

MySQL Advanced Functions

FunctionDescriptionSyntax
BINReturns a binary representation of a numberBIN( NUM )
BINARYConverts a value to a binary stringBINARY value
CASEGoes through conditions and return a value when the first condition is metCASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;
CAST
Converts a value (of any type) into a specified datatype
CAST(value AS datatype)
COALESCEReturns the first non-null value in a listCOALESCE(value_1, value_2, …., value_n)
CONNECTION IDReturns the unique connection ID for the current connectionCONNECTION_ID()
CONVConverts a number from one numeric base system to anotherCONV(number, current_base, new_base)
CONVERTConverts a value into the specified datatype or character setSyntax for converting datatypes:
CONVERT( input_value, data_type )
Syntax for converting character sets:
CONVERT( input_value USING character_set )
CURRENT USERReturns the user name and host name for the MySQL account that the server used to authenticate the current clientSELECT CURRENT_USER() ;
DATABASEReturns the name of the current databaseSELECT DATABASE();
IFReturns a value if a condition is TRUE, or another value if a condition is FALSEIF(condition, true_value, false_value)
IFNULL
Return a specified value if the expression is NULL, otherwise return the expression
IFNULL(expressionalt_value)
ISNULLReturns 1 or 0 depending on whether an expression is NULLISNULL(expression)
LAST INSERT IDReturns the AUTO_INCREMENT id of the last row that has been inserted or updated in a tableLAST_INSERT_ID(expression)
NULLIFCompares two expressions and returns NULL if they are equal. Otherwise, the first expression is returnedNULLIF(expr1expr2)
SESSION USERReturns the current MySQL user name and host nameCURRENT_USER()
SYSTEM USERReturns the current MySQL user name and host nameSYSTEM_USER()
USERReturns the current MySQL user name and host nameUSER()
VERSIONReturns the current version of the MySQL databaseVERSION()
MySQL Advanced Functions

Learn MySQL Functions with video

MySQL Functions with Examples Video credit : Steve Griffith

To Read More :-

1. Aggregate Functions in MySQL

2. Python Lists Tutorial 2021

3. Python Tuples Tutorial

4. Python Dictionaries Tutorial

MySQL Functions with Examples

FAQ :-

MySQL Functions with Examples

Q 1. What are MySQL Functions?

Ans. What is a function in MySQL? In MySQL, a function is a stored program that you can pass parameters into and then return a value.

Q 2. How do I create a stored function in MySQL?

Ans. First, specify the name of the stored function that you want to create after CREATE FUNCTION keywords. Third, specify the data type of the return value in the RETURNS statement, which can be any valid MySQL data types. Fourth, specify if a function is deterministic or not using the DETERMINISTIC keyword.

Q 3. What is MySQL what are its function and features?

Ans. MySQL Functions

MySQL is a relational database management system (RDBMS) based on the SQL (Structured Query Language) queries. It is one of the most popular languages for accessing and managing the records in the table. MySQL is open-source and free software under the GNU license.

Q 4. What are triggers in MySQL?

Ans. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.

MySQL Functions with Examples

Q 5. Why is MySQL used?

Ans. MySQL is a relational database management system based on SQL – Structured Query Language.The most common use for MySQL however, is for the purpose of a web database. It can be used to store anything from a single record of information to an entire inventory of available products for an online store.

In this content MySQL Functions are completely covered.

MySQL Functions with Examples

Q 6. Why are SQL functions used?

Ans. SQL functions are sub-programs, which are commonly used and re-used throughout SQL database applications for processing or manipulating data. All SQL database systems have DDL (data definition language) and DML (data manipulation language) tools to support the creation and maintenance of databases.

MySQL Functions with Examples

Q 7. What are different types of functions in SQL?

Ans.

We three types of user-defined functions.

  • Scalar Function. The user-defined scalar function also returns a single value as a result of actions performed by the function. …
  • Inline Table-Valued Function. …
  • Multi-Statement Table-Valued Function.

Q 8. WHAT IS function and procedure in SQL?

Ans. The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values. Functions can have only input parameters for it whereas Procedures can have input or output parameters. Functions can be called from Procedure whereas Procedures cannot be called from a Function.

MySQL Functions with Examples

MySQL Functions other contents.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *