Open In App

Various String, Numeric, and Date & Time functions in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

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 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 and so on. Although MySQL offers a big a big bouquet of functions, yet in this discussion we shall remain limited to some someone functions.

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

S.No. Function Description Examples
1. CHAR() 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” ;

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

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” ;

3. Date and Time Functions :
Date functions operate on values of the DATE datatype.

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() ;


Last Updated : 16 Dec, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads