MySQL Built-In Function
MySql Numeric Functions
• MOD(Modulus) – Divides the first number with second number and
returns the remainder.
• Syntax – Select MOD(12,4);
• POWER/POW – This function returns second number raised to the
power of the first number.
• Syntax – Select power(4,2);
• ROUND – This function rounds off the given number upto a given
number of digits (for decimal points).
• Syntax – Select round(15.753,1);
• Syntax – Select round(157.6,-1); (To find the nearest tens)
MySql NumericMySql Numeric Functions
Functions
• SIGN– This function returns the sign of given number. 1 for positive,-1
for negative and 0 for zero.
• Syntax – Select sign(-30);
• SQRT– This function returns the square root of given number.
• Syntax – Select SQRT(25);
• TRUNCATE – This function truncate the given number for given
number of decimal places.
• Syntax – Select TRUNCATE(15.29,1);
• Syntax – Select TRUNCATE(15.6,-1); (To find the nearest tens)
MySql String/Character Functions
• CHAR–Returns character of the given ASCII Code
• According to ASCII Capital A starts with 65, and Small a starts with 98.
• Syntax – Select char(65);
• Syntax – Select char(70,65,67,69)
• CONCAT– It concat two different strings.
• Syntax – Select CONCAT(‘RAMESH’,’KUMAR’);
• Syntax – Select CONCAT(fname,middlename,lastname);
MySql String/Character Functions
• LOWER/LCASE–It converts the arguments into lower case
• Syntax – Select lower(‘RAMESH’);
• UPPER/UCASE– It converts the arguments into upper case
• Syntax – Select upper(‘RAMESH’);
• SUBSTR(Sub String) – When you want to extract a part of the string
from a string.
• Syntax – Select SUBSTR(string,postion number,number of character)
• Syntax – Select SUBSTR(‘JOHNSON’,5,3);
• Syntax - Select SUBSTR(‘JOHNSON’,-7,4);
MySql String/Character Functions
• LTRIM–This function removes the white spaces from the beginning.
• Syntax – Select LTRIM(‘ SQL’);
• RTRIM–This function removes the white spaces from last.
• Syntax – Select RTRIM(‘SQL ’);
• TRIM– This function removes the white spaces from start as well as from
last part of the string.
• Syntax – Select TRIM(‘ SQL ’);
• Syntax - Select TRIM(LEADING ‘x’ from ‘xxSQLxx’);
• Syntax - Select TRIM(TRAILING ‘x’ from ‘xxSQLxx’);
• Syntax - Select TRIM(BOTH ‘x’ from ‘xxSQLxx’);
MySql String/Character Functions
• INSTR–This function searches second string in first string.
• Syntax – Select INSTR(‘JOHNSON’,’son’);
• LENGTH–Returns the length of the string.
• Syntax – Select LENGTH(‘AMIT’);
• Syntax – Select LENGTH(‘AMIT KUMAR’);
• LEFT– This function returns given number of character from the left side.
• Syntax – Select LEFT(‘Amit Kumar’,4);
• RIGHT - This function returns given number of character from the left side.
• Syntax – Select RIGHT(‘Amit Kumar’,5);