Functions in MySQL | Numeric, String and Date Time Functions in MySQL
Last Updated :
06 Sep, 2024
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. | Function | Description | Examples |
|---|
| 1. | definitions | Returns 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 spaces | SELECT TRIM('Bar One') ; |
| 7. | LENGTH() | Returns the length of a string in bytes | SELECT 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. | Function | Description | Example |
|---|
| 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 expression | SELECT 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 point | DRLRCT 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. | Function | Description | Example |
|---|
| 1 | CURDATE()/ CURRENT_DATE()/ CURRENT_DATE | Returns the current date. | SELECT CURDATE() ; |
| 2 | DATE() | Extracts the date part of a date or date-time expression. | SELECT DATE('2020-12-31 01:02:03') ; |
| 3 | MONTH() | Returns the month from the date passed. | SELECT MONTH('2020-12-31') ; |
| 4 | YEAR() | Returns the year | SELECT YEAR('2020-12-31') ; |
| 5 | NOW() | Returns the time at which the function executes. | SELECT NOW() ; |
| 6 | SYSDATE() | 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.
Similar Reads
Date and Time Functions in PL/SQL Date and Time Functions in PL/SQL are useful for date and time operations like extracting date components, performing date arithmetic, and converting between date formats. Date and Time Functions in PL/SQLThere are many useful Date and Time functions in PL/SQL. A table of such functions along with t
4 min read
TO_DAYS Function and WEEK Function in MariaDB TO_DAYS Function : In MariaDB, TO_DAYS Function converts a date into numeric days. In this function, the first parameter will be the Date. This function returns the numeric days of the date. This function is to be used only with dates within the Gregorian calendar. This function will return NULL if
2 min read
MySQL Date and Time Functions Handling date and time data in MySQL is essential for many database operations, especially when it comes to handling timestamps, scheduling tasks, or generating time-based. MySQL provides a variety of date and time functions that help users work with date values, perform calculations, and format the
6 min read
SECOND Function and SYSDATE Function in MariaDB 1. SECOND Function : In MariaDB, The SECOND Function returns the second portion of a date value. In this function, the first parameter will be the date/DateTime. This function returns the seconds (a number from 0 to 59) given a date value. In this function, we will pass the date value in it and it w
2 min read
STR_TO_DATE() function in MySQL STR_TO_DATE() : This function in MySQL helps to convert string values to date or time or DateTime values. The function will return zero (0000-00-00) if an empty string is passed as an argument. Syntax : STR_TO_DATE(string, format) Parameters : string -The string which will be converted to DateTime.
2 min read