SQL Built-in Functions
-
MySQL has lot of built-in functions. Let's discuss few of them.
lower()
-
lower() Converts all the character to lower case.
Syntax
SELECT LOWER('string');
​
SLECT LCASE('string');
​
-
string is required
upper()
-
upper() converts all the character to upper case.
Syntax
SELECT UPPER('string');
​
SLECT UCASE('string');
​
-
string is required
ltrim()
-
remove space from the left side of the string.
Syntax
SELECT LTRIM(' value ');
​
-
value is required
rtrim()
-
remove space from the right side of the string.
Syntax
SELECT RTRIM(' value ');
​
-
value is required
trim()
-
remove space from both side of the string.
substr()
-
helps to get the substring from the string.
Syntax
SELECT SUBSTR('string', start, length);
​
SELECT SUBSTRING('string', start, length);
​
-
string is required
-
start is required and if the number is positive counting start from left side and number is negative then counting start from right side.
-
length is optional, if not given then whole string from the start position is considered
RIGHT()
-
helps to get the substring from the string starting from the right.
Syntax
SELECT RIGHT('string', length);
​
-
string is required
-
length is required
LEFT()
-
helps to get the substring from the string starting from the left.
Syntax
SELECT LEFT('string', length);
​
-
string is required
-
length is required
LOCATE()
-
helps to get the position of a string.
Syntax
SELECT LOCATE('substring', 'string', start);
​
​
-
substring is required
-
string is required
-
start is optional
POSITION()
-
helps to get the position of a string.
Syntax
SELECT POSITION('substring' IN 'string');
​
​
-
substring is required
-
string is required
REVERSE()
-
helps to reverse a string.
Syntax
SELECT REVERSE('string');
​
​
-
string is required
REPLACE()
-
helps to replace a substring of a string with new substring.
Syntax
SELECT REPLACE('string', 'substring', 'newstring');
​
​
-
string is required
-
substring is required
-
newstring is required
REPEAT()
-
helps to repeat a string number of times.
Syntax
SELECT REPEAT('string', count);
​
​
-
string is required
-
count is required.
STRCMP()
-
helps to compare to two string.
Syntax
SELECT STRCMP('string_1', 'string_2');
​
​
-
string_1 is required
-
string_2 is required.
-
if s1 > s2 returns 1
-
if s1 < s2 returns -1
-
if s1 = s2 returns 0
CONCAT()
-
helps to concat two or more strings
Syntax
SELECT CONCAT('string_1', 'string_2' .......);
​
​
-
string_1 is required
-
string_2 is required.​
CONCAT_WS()
-
helps to concat two or more strings with a seperator
Syntax
SELECT CONCAT_WS(seperator, 'string_1', 'string_2' .......);
​
​
-
seperator is required.
-
string_1 is required.
-
string_2 is required.
ABS()
-
helps to get the absolute number.
Syntax
SELECT ABS(value);
​
​
-
value is required.
FLOOR()
-
helps to get the floor value.
Syntax
SELECT FLOOR(value);
​
​
-
value is required.
CEILING()
-
helps to get the ceiling value.
Syntax
SELECT CEILING(value);
​
SELECT CEIL(value);
​
-
value is required.
LEAST()
-
helps to get the smallest value from the list.
Syntax
SELECT LEAST(list);
​
​
-
list is required.
GREATEST()
-
helps to get the greatest value from the list.
Syntax
SELECT GREATEST(list);
​
​
-
list is required.
MOD()
-
helps to get the remainder
Syntax
SELECT MOD(a%b);
​
​
-
a & b is required.
POWER()
-
helps to get the raise to value.
Syntax
SELECT POWER(a%b);
​
SELECT POW(a%b);
​
-
a & b is required.
RAND()
-
helps to get the random value
Syntax
SELECT RAND();​
TRUNCATE()
-
helps to get the truncate the decimal places
Syntax
SELECT TRUNCATE(value, decimal_place);​
​
-
value is required.
-
decimal_place is required.
SQRT()
-
helps to get the square root of a number.
Syntax
SELECT SQRT(value);​
​
-
value is required.
now()
-
helps to get the current date and time.
Syntax
SELECT now();​​
monthname()
-
helps to get the month name from the date
Syntax
SELECT monthname(date);​​
There are more date functions which we can learn from the below link. It is the official documentation of MySQL.
There are lot more we can explore from the official documentation of MySQL. Do visit and explore new things.