MySQL: MySQL String Functions

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

MySQL String Functions

Syntax	Description	Function
1 2 SELECT CONCAT('Hello' , 'World') -- Result: 'HelloWorld'	Returns text strings concatenated	CONCAT
1 2 SELECT INSTR('hello' , 'e') -- Result: 2	Returns the location of a substring in a string.	INSTR
1 2 SELECT LENGTH('hello') -- Result: 5	Returns the number of characters of the specified string expression.	LENGTH
1 2 SELECT RTRIM(' hello    ') -- Result: ' hello'	Returns a character string after truncating all trailing blanks.	RTRIM
1 2 SELECT LTRIM('  hello    ') -- Result: 'hello    '	Returns a character expression after it removes leading blanks.	LTRIM
1 2 SELECT REPLACE('hello' , 'e' , '$') -- Result: 'h$llo'	Replaces all occurrences of a specified string value with another string value.	REPLACE
1 2 SELECT REVERSE('hello') -- Result: 'olleh'	Returns the reverse order of a string value.	REVERSE
1 2 SELECT SUBSTR('hello' , 2,3) -- Result: 'ell'	Returns part of a text.	SUBSTR
1 2 SELECT LOWER('HELLO') -- Result: 'hello'	Returns a character expression after converting uppercase character data to lowercase.	LOWER
1 2 SELECT UPPER('hello') -- Result: 'HELLO'	Returns a character expression with lowercase character data converted to uppercase.	UPPER
1 2 SELECT SELECT CONCAT('hello', space(3), 'world') -- Result: 'hello   world'	Returns a string consisting of N spaces	SPACE

MySQL Date Functions

Syntax	Description	Function
1 2 3 4 5 6 SELECT DATE_ADD('2008-01-02', INTERVAL 1 DAY); -- Result : 2008-01-03 SELECT DATE_ADD('2008-01-02', INTERVAL 1 WEEK); -- Result : 2008-01-09 SELECT DATE_ADD('2008-01-02', INTERVAL 1 MONTH); -- Result : 2008-02-02	Returns a specified date with additional time values.	DATE_ADD
1 2 SELECT DAYOFMONTH('2015-08-30') -- Result : 30	Returns an integer representing the day (day of the month) of the specified date.	DAYOFMONTH
1 2 SELECT LAST_DAY('2015-08-02') -- Result : 2015-08-31	Returns a date representing the last day of the month for specified date.	LAST_DAY
1 2 SELECT DATEDIFF('2010-04-01', '2010-03-01') -- Result : 31	Returns the difference between two days, expressed as a value in days.	DATEDIFF
1 2 SELECT PERIOD_DIFF(201005, 201003) -- Result : 2	returns the number of months between two periods.	PREIOD_DIFF
1 2 SELECT SYSDATE -- Result: (current date)	Returns the current database system date. This value is derived from the operating system of the computer on which the instance of MySQL is running.	SYSDATE()

MySQL Numeric Functions

Syntax	Description	Function
1 2 SELECT FLOOR(59.9) -- Result: 59	Returns an integer that is less than or equal to the specified numeric expression.	FLOOR
1 2 SELECT CEIL(59.1) -- Result: 60	Returns an integer that is greater than, or equal to, the specified numeric expression.	CEIL
1 2 3 4 5 SELECT ROUND(59.9) -- Result: 60   SELECT ROUND(59.1) -- Result: 59	Returns a numeric value, rounded to the specified length or precision.	ROUND

MySQL Conversion Functions

Syntax	Description	Function
1 2 SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d') -- 2015-09-16	Converts a a date into a string	DATE_FORMAT
1 2 SELECT FORMAT(1003423 , 3) -- Result : 1,003,423.000	Converts a number into a string	FORMAT
1 2 SELECT CONVERT('11', UNSIGNED INTEGER) -- Result 11	Used to convert one datatype into another, may be used to convert a string into a number	CONVERT

MySQL NULL-Related Functions

Syntax	Description	Function
1 2 SELECT IFNULL(NULL, 'Hello') -- Hello	Accepts two arguments and returns the first if its not NULL	IFNULL