Built in functions in MySQL

String Functions

String functions can manipulate a text string LEFT(string,length) – It is used to get the specified portion of the string from left side. For example – below query will show only first 3 letters of the name.

 
SELECT LEFT(FirstName,3) Name FROM CUSTOMER;

RIGHT(string,length) – It is used to get the specified portion of the string from right side. For example – below query will show only last 3 letters of the name.

 
SELECT RIGHT(FirstName,3) Name FROM CUSTOMER;

MID(string,start_position,length) – It is used to get the specified portion of the string from any position. For example – below query will show 3 letters of the name starting at position 2.

 
SELECT MID(FirstName,2,3) Name FROM CUSTOMER;

LENGTH(string) – It is used to get the length of the string. For example – below query will show length of the first name of the customer.

 
SELECT LENGTH(FirstName) NameLength FROM CUSTOMER;

LCASE(string) – It is used to convert the string to lower case.

 
SELECT LCASE(FirstName) Name FROM CUSTOMER;

UCASE(string) – It is used to convert the string to upper case.

 
SELECT UCASE(FirstName) Name FROM CUSTOMER;

REVERSE(string) – It is used to reverse the string.

 
SELECT REVERSE(FirstName) Name FROM CUSTOMER;

SUBSTRING(string,position) – It is used to get the SUBSTRING of the string from starting from specific position. For example – below query will show substring starting at position 2.

 
SELECT SUBSTRING(FirstName,2) Name FROM CUSTOMER;

CONCAT(string1,string2,…) – It is used to concatinate the strings together in one string.

 
SELECT CONCAT(FirstName, ” ” , LastName) Name FROM CUSTOMER;

REPLACE(whole_string,to_be_replaced,replacement) – It is used to replace the portion of the string. For example – below query will replace Sh by sa in name. Replacement is case sensitive.

 
SELECT REPLACE(FirstName,”Sh”,”sa”) Name FROM CUSTOMER;

INSERT(string,start_position,lengthToBeReplaced,newstring) – It is used to insert the substring in another string at specific position. For example – below query will insert Brett in name starting at position 2 and one letter from original string will be removed at position 2 as well.

 
SELECT INSERT(FirstName,2,1,”Brett”) Name FROM CUSTOMER;

LOCATE(substring,string) – It is used to find the substring in another string. If the substring is found, it’s starting position is returned. If substring is not found, 0 is returned. For example – below query will find the Sh in First Name.

 
SELECT LOCATE(“Sh”,FirstName) Position FROM CUSTOMER;

Numeric Functions

Numeric functions are used to manipulate numbers FlOOR function returns the nearest integer number that is less than given number SELECT FLOOR(number)

 
SELECT FLOOR(3.1);
-- Output will be 3.

CEILING function returns the nearest integer number that is larger than given number. SELECT CEILING(number)

 
SELECT CEILING(4.8);
-- Output will be 5.

ROUND function rounds the number upto given decimals. By default, it rounds the number to 0 decimal places. ROUND(number,[Decimal Places])

 
SELECT ROUND(12.812,2);
-- Output will be 12.81

 
SELECT ROUND(12.816,2);
-- Output will be 12.82

TRUNCATE function simply truncates the number upto given decimals. TRUNCATE(number,decimal places)

 
SELECT TRUNCATE(12.812,1);

Summarizing Functions

COUNT function returns the total number of records.

 
COUNT(ColumnName)
select count(*) from CUSTOMER.
-- It will print total number of records from the table CUSTOMER.

AVG function returns the average of all values in a given column.

 
SELECT AVG(ColumnName)

MIN function returns the minimum of all values in a given column.

 
MIN(ColumnName)

MAX function returns the maximum of all values in a given column.

 
MAX(ColumnName)

SUM function returns the sum of all values in a given column.

 
SUM(ColumnName)

Functions to manage Null values

COALESCE Function This function returns first Non-Null value from given parameters. If there is no non-null value, it returns null.

 
SELECT COALESCE(NULL, NULL, 5);
-- It will return 5.

SELECT COALESCE(NULL, NULL, NULL, NULL);
-- It will return NULL.

IFNULL function If first expression is null, it returns second expression. If first expression is not null, it returns first expression. IFNULL(original_value, new_value)

 
SELECT IFNULL(1,4);
-- It will return 1.

SELECT IFNULL(NULL,2);
-- It will return 2.

Date and Time Functions CURDATE method returns current date.

 
SELECT CURDATE();

CURTIME method returns current time.

 
SELECT CURTIME();

NOW method returns current date as well as time.

 
SELECT NOW();

DAYOFMONTH(date) – Returns numeric value of day (1-31)

 
SELECT DAYOFMONTH(STR_TO_DATE(’09/01/1986′, ‘%d/%m/%Y’));
-- Output will be 09.

DAYOFYEAR(date) – Returns numeric value of day in a year (1-365)

 
SELECT DAYOFYEAR(STR_TO_DATE(’09/01/1986′, ‘%d/%m/%Y’));
-- Output will be 09.

MONTH(date) – Returns numeric value of month (1-12)

 
SELECT MONTH(STR_TO_DATE(’09/01/1986′, ‘%d/%m/%Y’));
-- Output will be 01.

• DAYNAME(date) – Returns the name of weekday (sunday, monday etc.)
• MONTHNAME(date) – Returns the name of the month (January, Feb etc)
• YEAR(date) – Returns Year in 4 digits
• HOUR(time) – returns hour in 24 hour format
• MINUTE(time) – returns Minutes portion of the time
• SECOND(time) – returns seconds portion of the time
• DATE_FORMAT() – It is used to format the DATE, DATETIME and TIMESTAMP.

To get the date in dd-mm-yyyy format, use below syntax

 
DATE_FORMAT(NOW(),’%d-%m-%Y’)

To get the date in dd-mmm-yyyy format, use below syntax

 
DATE_FORMAT(NOW(),’%d-%b-%Y’)

-- hour:min:sec format
DATE_FORMAT(NOW(),’%h:%i:%s %p’)

To get the date in 24 hour format, use below syntax

 
DATE_FORMAT(NOW(),’%T’)

TIME_FORMAT() – It is used to format the TIME, DATETIME and TIMESTAMP.

 
TIME_FORMAT(NOW(),’%h:%i:%s %p’)

DATE_ADD – It is used to add the interval to given date

 
DATE_ADD(date,INTERVAL expr type)
SELECT DATE_ADD(now(), INTERVAL 5 DAY)
SELECT DATE_ADD(now(), INTERVAL 5 HOUR)
SELECT DATE_ADD(now(), INTERVAL 5 MONTH)
SELECT DATE_ADD(now(), INTERVAL 5 YEAR)

DATE_SUB(date,INTERVAL expr type) – It is used to subtract the interval from given date

 
SELECT DATE_SUB(now(), INTERVAL 5 MINUTE)
SELECT DATE_SUB(now(), INTERVAL 5 WEEK)
SELECT DATE_SUB(now(), INTERVAL 5 QUARTER)