Open In App

Functions in MySQL | Numeric, String and Date Time Functions in MySQL

Last Updated : 06 Sep, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

In MySQL, functions play a crucial role in performing various operations on data, such as calculations, string manipulations, and date handling. These built-in functions simplify complex queries and data transformations, making it easier to manage and analyze data within a database.

In this article, we will look at these different categories of MySQL functions, and look at different MySQL functions with definitions and examples in each category.

Functions in MySQL

  • In MySQL, functions are a fundamental part of the SQL language, enabling us to perform calculations, manipulate data and retrieve information.
  • The functions in MySQL can edit rows and tables, alter strings, and help us to manage organized and easy-to-navigate databases.
  • A function is a special type of predefined command set that performs some operation and returns a value. Functions operate on zero, one, two, or more values that are provided to them.
  • The values that are provided to functions are called parameters or arguments.

The MySQL functions have been categorized into various categories, such as String functions, Mathematical functions, Date and Time functions, etc.

MySQL String Functions

The string functions of MySQL are used to manipulate and transform the text string. Some commonly used string functions are:

S.No.FunctionDescriptionExamples
1.definitionsReturns the character for each integer passes

1. SELECT CHAR(70, 65, 67, 69) ;

2. SELECT CHAR(65, 67.3, 69.3) ;

2.CONCAT()Returns concatenated string

SELECT CONCAT(name, aggregate) AS "Name Marks"

FROM student WHERE age = 14 OR age = 16;

3.LOWER() /LCASE()Returns the argument in lowercase

SELECT LOWER('GEEKSFORGEEKS') AS "LowerName1",

LOWER('Geeks For Geeks') AS "LowerName2" ;

4.SUBSTRING(), SUBSTR()Returns the substring as specified

1. SELECT SUBSTR('ABSDEFG', 3, 4) "Subs" ;

2. SELECT SUBSTR('ABCDEFG', -5, 4) "Subs" ;

5.UPPER()/UCASE()Converts to uppercase

SELECT UPPER('Large') "Uppercase" ;

or SELECT UCASE('Large') "Uppercase";

6.TRIM()Removes leading and trailing spacesSELECT TRIM('Bar One') ;
7.LENGTH()Returns the length of a string in bytesSELECT LENGTH('CANDIDE') "Length in characters" ;

MySQL Numeric Functions

The numeric functions in MySQL accept numeric values, perform a mathematic operation on the values and return resulting sheet. Some useful numeric functions are:

S.No.FunctionDescriptionExample
1.MOD()Returns the remainder of one expression by diving y another expression.SELECT 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 points.SELECT ROUND(15.193, 1) "Round" ;
4.SIGN()This function returns sign of a given number.SELECT SIGN(-15) "Sign" ;
5.SQRT()Returns the non-negative square root of numeric expression.SELECT SQRT(26) "Square root" ;
6.TRUNCATE()Returns numeric exp1 truncate to exp2 decimal places. If exp2 is 0, then the result will have no decimal pointDRLRCT TRUNCATE(15.79, 1) "Truncate" ;

MySQL Date and Time Functions

Date and Time functions in SQL are used to manipulate and transform date and time data stored in tables. Date functions operate on values of the DATE datatype.

Some useful date and time functions are:

S.No.FunctionDescriptionExample
1

CURDATE()/

CURRENT_DATE()/

CURRENT_DATE

Returns the current date.SELECT CURDATE() ;
2DATE()Extracts the date part of a date or date-time expression.SELECT DATE('2020-12-31 01:02:03') ;
3MONTH()Returns the month from the date passed.SELECT MONTH('2020-12-31') ;
4YEAR()Returns the yearSELECT YEAR('2020-12-31') ;
5NOW()Returns the time at which the function executes.SELECT NOW() ;
6SYSDATE()Returns the current date and time.

SELECT NOW(), SLEEP(2), NOW() ;

or SELECT SYSDATE(), SLEEP(2), SYSDATE() ;

Conclusion

MySQL functions are indispensable tools for database management, providing a range of capabilities from manipulating strings and performing mathematical operations to managing date and time data. By using these functions, users can streamline data retrieval and transformation processes, ensuring more effective and organized database operations.


Next Article
SECOND Function and SYSDATE Function in MariaDB

N
nitishkumarpatel191
Improve
Article Tags :

Similar Reads

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox