[IT] MySQL Functions

String Functions

• ASCII

  • Return the ASCII value for the specific character

• CHAR_LENGTH or CHARACTER_LENGTH

  • Return the length of string (in characters)

• CONCAT

  • Adds two or more expressions together

• CONCAT_WS

  • Adds two or more expressions together with a separator

• FIELD

  • Returns the index position of a value in a list of values

• FIND_IN_SET

  • Returns the position of a string within a list of strings

• FORMAT

  • Return sFormats a number to a format like ”#,###,###.##”, rounded to a specified number of decimal places

• INSERT

  • Inserts a string within a string at the specified position and for a certain number of characters

• INSTR

  • Returns the position of the first occurrence of a string in another string

• LCASE

  • Converts a string to lower-case

• LEFT

  • Extracts a number of characters from a string (starting from left)

• LENGTH

  • Returns the length of a string (in bytes)

• LOCATE

  • Returns the position of the first occurrence of a substring in a string

• LOWER

  • Converts a string to lower-case

• LPAD

  • Left-pads a string with another string, to a certain length

• LTRIM

  • Removes leading spaces from a string

• MID

  • Extracts a substring from a string (starting at any position)

• POSITION

  • Returns the position of the first occurrence of a substring in a string

• REPEAT

  • Repeats a string as many times as specified

• REPLACE

  • Replaces all occurrences of a substring within a string, with a new substring

• REVERSE

  • Reverses a string and returns the result

• RIGHT

  • Extracts a number of characters from a string (starting from right)

• RPAD

  • Right-pads a string with another string, to a certain length

• RTRIM

  • Removes trailing spaces from a string

• SPACE

  • Returns a string of the specified number of space characters

• STRCMP

  • Compares two strings

• SUBSTR

  • Extracts a substring from a string (starting at any position)

• SUBSTRING

  • Extracts a substring from a string (starting at any position)

• SUBSTRING_INDEX

  • Returns a substring of a string before a specified number of delimiter occurs

• TRIM

  • Removes leading and trailing spaces from a string

• UCASE

  • Converts a string to upper-case

• UPPER

  • Converts a string to upper-case

Numeric Functions

• ABS

  • Returns the absolute value of a number

• ACOS

  • Returns the arc cosine of a number

• ASIN

  • Returns the arc sine of a number

• ATAN

  • Returns the arc tangent of one or two numbers

• ATAN2

  • Returns the arc tangent of two numbers

• AVG

  • Returns the average value of an expression

• CEIL

  • Returns the smallest integer value that is >= to a number

• CEILING

  • Returns the smallest integer value that is >= to a number

• COS

  • Returns the cosine of a number

• COT

  • Returns the cotangent of a number

• COUNT

  • Returns the number of records returned by a select query

• DEGREES

  • Converts a value in radians to degrees

• DIV

  • Used for integer division

• EXP

  • Returns e raised to the power of a specified number

• FLOOR

  • Returns the largest integer value that is <= to a number

• GREATEST

  • Returns the greatest value of the list of arguments

• LEAST

  • Returns the smallest value of the list of arguments

• LN

  • Returns the natural logarithm of a number

• LOG

  • Returns the natural logarithm of a number, or the logarithm of a number to a specified base

• LOG10

  • Returns the natural logarithm of a number to base 10

• LOG2

  • Returns the natural logarithm of a number to base 2

• MAX

  • Returns the maximum value in a set of values

• MIN

  • Returns the minimum value in a set of values

• MOD

  • Returns the remainder of a number divided by another number

• PI

  • Returns the value of PI

• POW

  • Returns the value of a number raised to the power of another number

• POWER

  • Returns the value of a number raised to the power of another number

• RADIANS

  • Converts a degree value into radians

• RAND

  • Returns a random number

• ROUND

  • Rounds a number to a specified number of decimal places

• SIGN

  • Returns the sign of a number

• SIN

  • Returns the sine of a number

• SQRT

  • Returns the square root of a number

• SUM

  • Calculates the sum of a set of values

• TAN

  • Returns the tangent of a number

• TRUNCATE

  • Truncates a number to the specified number of decimal places

Date Functions

• ADDDATE

  • Adds a time/date interval to a date and then returns the date

• ADDTIME

  • Adds a time interval to a time/datetime and then returns the time/datetime

• CURDATE

  • Returns the current date

• CURRENT_DATE

  • Returns the current date

• CURRENT_TIME

  • Returns the current time

• CURRENT_TIMESTAMP

  • Returns the current date and time

• CURTIME

  • Returns the current time

• DATE

  • Extracts the date part from a datetime expression

• DATEDIFF

  • Returns the number of days between two date values

• DATE_ADD

  • Adds a time/date interval to a date and then returns the date

• DATE_FORMAT

  • Formats a date

• DATE_SUB

  • Subtracts a time/date interval from a date and then returns the date

• DAY

  • Returns the day of the month for a given date

• DAYNAME

  • Returns the weekday name for a given date

• DAYOFMONTH

  • Returns the day of the month for a given date

• DAYOFWEEK

  • Returns the weekday index for a given date

• DAYOFYEAR

  • Returns the day of the year for a given date

• EXTRACT

  • Extracts a part from a given date

• FROM_DAYS

  • Returns a date from a numeric datevalue

• HOUR

  • Returns the hour part for a given date

• LAST_DAY

  • Extracts the last day of the month for a given date

• LOCALTIME

  • Returns the current date and time

• LOCALTIMESTAMP

  • Returns the current date and time

• MAKEDATE

  • Creates and returns a date based on a year and a number of days value

• MAKETIME

  • Creates and returns a time based on an hour, minute, and second value

• MICROSECOND

  • Returns the microsecond part of a time/datetime

• MINUTE

  • Returns the minute part of a time/datetime

• MONTH

  • Returns the month part for a given date

• MONTHNAME

  • Returns the name of the month for a given date

• NOW

  • Returns the current date and time

• PERIOD_ADD

  • Adds a specified number of months to a period

• PERIOD_DIFF

  • Returns the difference between two periods

• QUARTER

  • Returns the quarter of the year for a given date value

• SECOND

  • Returns the seconds part of a time/datetime

• SEC_TO_TIME

  • Returns a time value based on the specified seconds

• STR_TO_DATE

  • Returns a date based on a string and a format

• SUBDATE

  • Subtracts a time/date interval from a date and then returns the date

• SUBTIME

  • Subtracts a time interval from a datetime and then returns the time/datetime

• SYSDATE

  • Returns the current date and time

• TIME

  • Extracts the time part from a given time/datetime

• TIME_FORMAT

  • Formats a time by a specified format

• TIME_TO_SEC

  • Converts a time value into seconds

• TIMEDIFF

  • Returns the difference between two time/datetime expressions

• TIMESTAMP

  • Returns a datetime value based on a date or datetime value

• TO_DAYS

  • Returns the number of days between a date and date “0000-00-00”

• WEEK

  • Returns the week number for a given date

• WEEKDAY

  • Returns the weekday number for a given date

• WEEKOFYEAR

  • Returns the week number for a given date

• YEAR

  • Returns the year part for a given date

• YEARWEEK

  • Returns the year and week number for a given date

Advanced Functions

• BIN

  • Returns a binary representation of a number

• BINARY

  • Converts a value to a binary string

• CASE

  • Goes through conditions and return a value when the first condition is met

• CAST

  • Converts a value (of any type) into a specified datatype

• COALESCE

  • Returns the first non-null value in a list

• CONNECTION_ID

  • Returns the unique connection ID for the current connection

• CONV

  • Converts a number from one numeric base system to another

• CONVERT

  • Converts a value into the specified datatype or character set

• CURRENT_USER

  • Returns the user name and host name for the MySQL account that the server used to authenticate the current client

• DATABASE

  • Returns the name of the current database

• IF

  • Returns a value if a condition is TRUE, or another value if a condition is FALSE

• IFNULL

  • Return a specified value if the expression is NULL, otherwise return the expression

• ISNULL

  • Returns 1 or 0 depending on whether an expression is NULL

• LAST_INSERT_ID

  • Returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table

• NULLIF

  • Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned

• SESSION_USER

  • Returns the current MySQL user name and host name

• SYSTEM_USER

  • Returns the current MySQL user name and host name

• USER

  • Returns the current MySQL user name and host name

• VERSION

  • Returns the current version of the MySQL database