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)

You may also like...