Skip to content
Related Articles

Related Articles

Various String, Numeric, and Date & Time functions in MySQL
  • Last Updated : 16 Dec, 2020

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.FunctionDescriptionExamples
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 spacesSELECT TRIM(‘Bar One’) ;
7.LENGTH()Returns the length of a string in bytesSELECT 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.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” ;

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

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

My Personal Notes arrow_drop_up
Recommended Articles
Page :