SQL supports a large number of scalar functions. In this appendix, we present the following for the functions supported by many SQL products: the name, a description, the data type of the result of the function, and a few examples. The functions are sorted by name.
Some functions have more than one name. To make the search easier, we have included them all, but we refer to the functions with the same name.
Description: This function returns the absolute value of a numeric expression.
Data type: Numeric
ABS(-25) ⇨ 25 ABS(-25.89) ⇨ 25.89
Description: This function returns, in radians, the angle size for any given arc cosine value. The value of the parameter must lie between -1 and 1 inclusive.
Data type: Numeric
ACOS(0) ⇨ 1.5707963267949 ACOS(-1) - PI() ⇨ 0 ACOS(1) ⇨ 0 ACOS(2) ⇨ NULL
Description: This function adds an interval (the second parameter) to a datestamp or timestamp expression (the first parameter). See Section 5.13.3, in Chapter 5, “SELECT Statement: Common Elements,” for specifying intervals. If the second parameter is not an interval but is a numeric value, SQL assumes that this value represents a number of days.
Date type: Date or timestamp
ADDDATE('2004-01-01', INTERVAL 5 MONTH) ⇨ '2004-06-01'
ADDDATE(TIMESTAMP('2004-01-01'), INTERVAL 5 MONTH)
⇨ '2004-06-01 00:00:00'
ADDDATE('2004-01-01 12:00:00', INTERVAL 5 DAY)
⇨ '2004-01-06 12:00:00'
ADDDATE('2004-01-01', 5) ⇨ '2004-01-06'
Description: This function adds two time expressions. The result is an interval consisting of a number of hours, minutes, and seconds. Therefore, the number of hours can be greater than 24.
Data type: Time
ADDTIME('12:59:00', '0:59:00') ⇨ '13:58:00'
ADDTIME('12:00:00', '0:00:00.001') ⇨ '12:00:00.001000'
ADDTIME('100:00:00', '900:00:00') ⇨ '1000:00:00'
Description: This function returns the ASCII value of the first character of an alphanumeric expression.
Data type: Numeric
ASCII('Database') ⇨ 68
ASCII('database') ⇨ 100
ASCII('') ⇨ 0
ASCII(NULL) ⇨ NULL
Description: This function returns, in radians, the angle size for any given arc sine value. The value of the parameter must lie between -1 and 1 inclusive; otherwise, the result is equal to the NULL value.
Data type: Numeric
ASIN(1) ⇨ 1.5707963267949 ASIN(0) ⇨ 0 ASIN(NULL) ⇨ NULL
Description: This function returns, in radians, the angle size for any given arc tangent value.
Data type: Numeric
ATAN(0) ⇨ 0 ATAN(100) ⇨ 1.56079666010823 ATAN(1) ⇨ 0.78539816339745
Description: This function returns, in radians, the angle size for any given arc tangent value.
Data type: Numeric
ATAN2(30,30) ⇨ 0.78539816339745 ATAN2(-1,-1) ⇨ -2.3561944901923
Description: This function returns the hyperbolic arc tangent value of the parameter that must be specified in radians.
Data type: Numeric
ATANH(0.4) ⇨ 0.255412811882995
Description: This function transforms the numeric value of the parameter into a binary value. This binary value consists of ones and zeroes, and has the alphanumeric data type.
Data type: Alphanumeric
BIN(7) ⇨ '111' BIN(1000000) ⇨ '11110100001001000000'
Description: This function shows the number of bits needed to present the value of the parameter. Here, 64-bit integers are used.
Data type: Numeric
BIT_COUNT(3) ⇨ 2 BIT_COUNT(-1) ⇨ 64
Description: This function returns the length in bits of an alphanumeric value.
Data type: Numeric
BIT_LENGTH('database') ⇨ 64
BIT_LENGTH(BIN(2)) ⇨ 16
Description: This function returns the highest whole number that is greater than or equal to the value of the parameter.
Data type: Numeric
CEILING(13.43) ⇨ 14 CEILING(-13.43) ⇨ -13 CEILING(13) ⇨ 13
Description: This function returns the alphanumeric character of the numeric parameter. See the CHR function.
Data type: Alphanumeric
CHAR(80) ⇨ 'P' CHAR(82) + CHAR(105) + CHAR(99) + CHAR(107) ⇨ 'Rick'
Description: This function returns the length of an alphanumeric expression.
Data type: Numeric
CHARACTER_LENGTH('database') ⇨ 8
CHARACTER_LENGTH((SELECT MAX(NAME) FROM PLAYERS)) ⇨ 6
CHARACTER_LENGTH('') ⇨ 0
CHARACTER_LENGTH(NULL) ⇨ NULL
CHARACTER_LENGTH(BIN(8)) ⇨ 4
Description: This function returns the name of the character set of the alphanumeric parameter.
Data type: Alphanumeric
CHARSET('database') ⇨ 'latin1'
CHARSET((SELECT MAX(NAME) FROM PLAYERS)) ⇨ 'latin1'
CHARSET((SELECT MAX(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES)) ⇨ 'utf8'
Description: This function returns the length of an alphanumeric expression. See the CHARACTER_LENGTH function.
Data type: Numeric
CHAR_LENGTH('database') ⇨ 8
CHAR_LENGTH((SELECT MAX(NAME) FROM PLAYERS)) ⇨ 6
CHAR_LENGTH('') ⇨ 0
CHAR_LENGTH(NULL) ⇨ NULL
CHAR_LENGTH(BIN(8)) ⇨ 4
Description: This function returns the alphanumeric character belonging to the numeric parameter. See the CHAR function.
Data type: Alphanumeric
CHR(80) ⇨ 'P' CHR(82) + CHR(105) + CHR(99) + CHR(107) ⇨ 'Rick'
Description: This function can have a variable number of parameters. The value of the function is equal to the value of the first parameter that is not equal to NULL.
If E1, E2, and E3 are three expressions, the specification:
COALESCE(E1, E2, E3)
is equivalent to the following case expression:
CASE WHEN E1 IS NOT NULL THEN E1 WHEN E2 IS NOT NULL THEN E2 WHEN E3 IS NOT NULL THEN E3 ELSE NULL END
Data type: Depends on parameters
COALESCE('John', 'Jim', NULL) ⇨ 'John'
COALESCE(NULL, NULL, NULL, 'John', 'Jim') ⇨ 'John'
Description: This function determines the coercibility value of an expression.
Data type: Numeric
COERCIBILITY(NULL) ⇨ 5
COERCIBILITY('Database') ⇨ 4
Description: This function gets the name of the collating sequence of the alphanumeric parameter.
Data type: Alphanumeric
COLLATION('database')
⇨ 'latin1_swedish_ci'
COLLATION((SELECT MAX(NAME) FROM PLAYERS))
⇨ 'latin1_swedish_ci'
COLLATION((SELECT MAX(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES))
⇨ 'utf8_general_ci'
Description: This function combines two alphanumeric values. The same effect can be obtained with the ‖ operator.
Data type: Alphanumeric
CONCAT('Data','base') ⇨ 'Database'
Description: This function returns the numeric identifier of the connection.
Data type: Numeric
CONNECTION_ID() ⇨ 4
Description: This function converts the value (first parameter) of one number base (second parameter) to another (third parameter). The value of the two last parameters must be between 2 and 36; otherwise, the result is equal to NULL. Furthermore, the value of the first parameter should fit into the number base of the first parameter; otherwise, the result is 0.
Data type: Alphanumeric
CONV(1110, 2, 10) ⇨ '14' CONV(1110, 10, 2) ⇨ '10001010110' CONV(1110, 10, 8) ⇨ '2126' CONV(1110, 10, 16) ⇨ '456' CONV(35, 10, 36) ⇨ 'Z' CONV(35, 10, 37) ⇨ NULL CONV(8, 2, 10) ⇨ '0'
Description: This function converts the data type of the first parameter. The second parameter must be equal to one of the well-known data types, including BINARY, CHAR, DATE, DATETIME, TIME, SIGNED, SIGNED INTEGER, UNSIGNED, UNSIGNED INTEGER, or VARCHAR. This specification:
CONVERT(par1, type1)
is equal to:
CAST(par1 AS type1)
The following formulations may also be used:
CONVERT(par1 USING type1)
Data type: Depends on the second parameter
CONVERT(45, CHAR(2)) ⇨ '45'
CONVERT('2000-01-42', DATE) ⇨ '2000-01-01'
CONVERT(12.56, UNSIGNED INTEGER) ⇨ 13
CONVERT(-12.56, UNSIGNED INTEGER) ⇨ 18446744073709551603
Description: This function determines what the timestamp value of a timestamp expression (first parameter) is when the time zone is changed. The second parameter indicates the current time zone, and the third parameter indicates the new time zone.
Date type: Timestamp
CONVERT_TZ('2005-05-20 09:30:40', '+00:00', '+9:00')
⇨ 2005-05-20 18:30:40
Description: This function returns, in radians, the cosine value for any angle size.
Data type: Numeric
COS(0) ⇨ 1 COS(PI()/2) ⇨ 0 COS(PI()) ⇨ –1
Description: This function returns, in radians, the cotangent value for any angle size.
Data type: Numeric
COT(10) ⇨ 1.54235 COT(PI()/2) ⇨ 0 COT(NULL) ⇨ NULL
Description: This function returns the system date. In some SQL products, to get the system date, the system variable SYSDATE should be used.
Data type: date
CURDATE() ⇨ '2005-02-20'
Description: This function returns the system date with the format YYYY-MM-DD. If the function is regarded as a numeric expression, the system date is presented as a numeric value with the format YYYYMMDD. If the brackets are left out, the function changes into the system variable CURRENT_DATE. See the CURDATE function.
Data type: Date or double
CURRENT_DATE() ⇨ '2005-02-20' CURRENT_DATE() + 0 ⇨ 20050220 CURRENT_DATE ⇨ '2005-02-20'
Description: This function returns the system time with the format HH:MM:SS. The abbreviation HH stands for the hours, MM for minutes, and SS for seconds. If the function is regarded as a numeric expression, the system time is presented as a numeric value with the format HHMMSS. If the brackets are left out, the function changes into the system variable CURRENT_TIME. See the CURTIME function.
Data type: Time or double
CURRENT_TIME() ⇨ '16:42:24' CURRENT_TIME() + 0 ⇨ 164224 CURRENT_TIME ⇨ '16:42:24'
Description: This function returns the system date and time with the format YYYY-MM-DD HH:MM:SS. The abbreviation YYYY stands for years, the first MM for months, DD for days, HH for hours, the second MM for minutes, and SS for seconds. If the function is regarded as a numeric expression, the system date and time are presented as a numeric value with the format YYYYMMDDHHMMSS. If the brackets are left out, the function changes into the system variable CURRENT_TIMESTAMP.
Data type: Timestamp or double
CURRENT_TIMESTAMP() ⇨ '2005-10-16 20:53:45' CURRENT_TIMESTAMP() + 0 ⇨ 20051016205345 CURRENT_TIMESTAMP ⇨ '2005-10-16 20:53:45'
Description: This function returns the name of the SQL user.
Data type: Alphanumeric
CURRENT_USER() ⇨ 'root@localhost'
Description: This function returns the system time with the format HH:MM:SS. The abbreviation HH stands for hours, MM for minutes, and SS for seconds. In some products, this function is briefly called TIME.
Data type: Alphanumeric
CURTIME() ⇨ '16:42:24'
Description: This function shows the name of the current database.
Data type: Alphanumeric
DATABASE() ⇨ 'TENNIS'
Description: This function transforms the parameter into a date value. The parameter should have the format of a correct date or timestamp.
Data type: Date
DATE('2005-12-01') ⇨ '2005-12-01'
DATE('2005-12-01 12:13:14') ⇨ '2005-12-01'
Description: This function adds an interval (the second parameter) to a date or timestamp expression (the first parameter). See Section 5.13.3, in Chapter 5, for specifying intervals. See the ADDDATE function.
Date type: Date or timestamp
DATE_ADD('2004-01-01', INTERVAL 5 MONTH) ⇨ '2004-06-01'
DATE_ADD('2004-01-01 12:00:00', INTERVAL 5 DAY)
⇨ '2004-01-06 12:00:00'
Description: This function calculates the number of days between two date or timestamp expressions.
Date type: Numeric
DATEDIFF('2004-01-12', '2004-01-01') ⇨ 11
DATEDIFF('2004-01-01', '2004-01-12') ⇨ -11
DATEDIFF('2004-01-12 19:00:00', '2004-01-01') ) ⇨ 11
DATEDIFF('2004-01-12 19:00:00', '2004-01-01 01:00:00') ⇨ 11
DATEDIFF('2004-01-12', CURDATE()) ⇨ -643
Description: This function transforms a date or timestamp expression (the first parameter) to an alphanumeric value. The second parameter describes the format of that alphanumeric value. Several special format strings can be used; see the following table.
FORMAT STRING | EXPLANATION |
|---|---|
| Three-letter English abbreviation of the weekday (for example, |
| Three-letter English abbreviation of the month (for example, |
| Numeric code for the month ( |
| Day of the month with an English suffix, such as |
| Two-digit numeric code for the day of the month ( |
| One-or two-digit numeric code for the day of the month ( |
| Six-digit numeric code for the number of microseconds ( |
| Two-digit numeric code for the hour ( |
| Two-digit numeric code for the hour ( |
| Two-digit numeric code for the hour ( |
| Two-digit numeric code for the number of minutes ( |
| Three-digit numeric code for the day of the year ( |
| One or two-digit numeric code for the hour ( |
| One or two-digit numeric code for the hour ( |
| English indication of the month (for example, |
| Two-digit, numeric code for the month ( |
| Indication of |
| Indication of the time (in 12 hours) with the format |
| Two-digit numeric code for the number of seconds ( |
| Two-digit numeric code for the number of seconds ( |
| Indication of the time (in 24 hours) with the format |
| Two-digit numeric code for the week in the year ( |
| Two-digit numeric code for the week in the year ( |
| Two-digit numeric code for the week in the year ( |
| Two-digit numeric code for the week in the year ( |
| English indication of the day in the week (for example, |
| One-digit code for the day in the week ( |
| Four-digit numeric code that indicates the year in which the week starts belonging to the specified date, for which Sunday is the first day of the week |
| Four-digit, numeric code that indicates the year in which the week starts belonging to the specified date, for which Monday is the first day of the week |
| Four-digit numeric code for the year |
| Two-digit numeric code for the year |
| Returns the percentage sign |
DATE_FORMAT('2005-10-16', '%a %c %b') ⇨ 'Sun 10 Oct'
DATE_FORMAT('2005-10-06', '%d %e %D') ⇨ '06 6 6th'
DATE_FORMAT('2005-01-16', '%j %M %m') ⇨ '016 January 01'
DATE_FORMAT('2005-01-09', '%U %u %V %v') ⇨ '02 01 02 01'
DATE_FORMAT('2005-12-31', '%U %u %V %v') ⇨ '52 52 52 52'
DATE_FORMAT('2005-01-09', '%W %w') ⇨ 'Sunday 0'
DATE_FORMAT('2005-01-02', '%X %x') ⇨ '2005 2004'
DATE_FORMAT('2005-01-09', '%Y %y') ⇨ '2005 05'
DATE_FORMAT('2005-01-01 12:13:14.012345', '%f') ⇨ '012345'
DATE_FORMAT('2005-01-01 12:13:14', '%H %h %I %i')
⇨ '13 01 01 14'
DATE_FORMAT('2005-01-01 12:13:14', '%k %l %p') ⇨ '12 12 PM'
DATE_FORMAT('2005-01-01 12:13:14', '%S %s %T')
⇨ '14 12 12:13:14'
DATE_FORMAT('2005-01-09', 'Database') ⇨ 'Database'
DATE_FORMAT('2005-01-09', 'It is this day %W')
⇨ 'This day is Sunday'
Description: This function subtracts an interval (the second parameter) from a date or timestamp expression (the first parameter). See Section 5.13.3, in Chapter 5, for specifying intervals. See the SUBDATE function.
Data type: Date or timestamp
DATE_SUB('2004-01-01', INTERVAL 5 MONTH) ⇨ '2003-08-01'
DATE_SUB('2004-01-01 12:00:00', INTERVAL 5 DAY)
⇨ '2003-12-27 12:00:00'
Description: This function returns the number of the day of the month from a date or timestamp expression. The value of the result is always a whole number between 1 and 31 inclusive. See the DAYOFMONTH function.
Date type: Numeric
DAY('2004-01-01') ⇨ 1
DAY('2004-01-01 09:11:11') ⇨ 1
DAY(CURRENT_DATE()) ⇨ 17
DAY(CURRENT_TIMESTAMP()) ⇨ 17
Description: This function returns the name of the day of the week from a date or timestamp expression.
Data type: Alphanumeric
DAYNAME('2005-01-01')⇨ 'Saturday'
Description: This function returns the number of the day of the month from a date or timestamp expression. The value of the result is always a whole number between 1 and 31 inclusive. See the DAY function.
Data type: Numeric
DAYOFMONTH('2004-01-01') ⇨ 1
DAYOFMONTH('2004-01-01 09:11:11') ⇨ 1
DAYOFMONTH(CURRENT_DATE()) ⇨ 17
DAYOFMONTH(CURRENT_TIMESTAMP()) ⇨ 17
Description: This function returns the number of the day of the week from a date or timestamp expression. The value of the result is always a whole number between 1 and 7 inclusive.
Data type: Numeric
DAYOFWEEK('1005-07-29') ⇨ 2
DAYOFWEEK(CURRENT_TIMESTAMP()) ⇨ 3
Description: This function returns the number of the day of the year from a date or timestamp expression. The value of the result is always a whole number between 1 and 366 inclusive.
Data type: Numeric
DAYOFYEAR('2005-07-29') ⇨ 210
DAYOFYEAR('2005-07-29 12:00:00') ⇨ 210
DAYOFYEAR(CURDATE()) ⇨ 291
Description: This function returns the default value of a certain column. See also Example 15.13, in Chapter 15, “Creating Tables.”
Data type: Depends on the column
DEFAULT(DATE) ⇨ '1990-01-01' DEFAULT(AMOUNT) ⇨ 50.00
Description: This function converts a number of degrees to a value in radians.
Data type: Numeric
DEGREES(1.570796) ⇨ 90 DEGREES(PI()) ⇨ 180
Description: This function returns the result of the number e to the power of x, where x is the value of the parameter and e the basis of natural logarithms.
Data type: Numeric
EXP(1) ⇨ 2.718281828459 EXP(2) ⇨ 7.3890560989307
Description: This function returns the smallest whole number that is less than or equal to the value of the parameter.
Data type: Numeric
FLOOR(13.9) ⇨ 13 FLOOR(-13.9) ⇨ -14
Description: This function formats a numeric value to the pattern nn,nnn,nnn.nnn. The second parameter represents the number of decimals behind the comma and must be greater than or equal to zero.
Data type: Alphanumeric
FORMAT(123456789.123, 2) ⇨ '123,456,789.12' FORMAT(123456789.123, 0) ⇨ '123,456,789'
Description: This function returns the number of rows in the result of the previous SELECT statement.
Data type: Numeric
FOUND_ROWS() ⇨ 14
Description: This function determines the date belonging to a number of days that have elapsed since the year 0. The parameter forms the number of days and must be between 366 and 3,652,424.
Data type: Date
FROM_DAYS(366) ⇨ '0001-01-01' FROM_DAYS(366*2000) ⇨ '2004-02-24' FROM_DAYS(3652424) ⇨ '9999-12-31' FROM_DAYS(3652500) ⇨ '0000-00-00' FROM_DAYS(3652424) – INTERVAL 5 DAY ⇨ '9999-12-26'
Description: This function returns a format that can be used in other functions, such as DATE_FORMAT, TIME_FORMAT, and STR_TO_DATE. The first parameter represents the data type. This must be equal to DATE, TIME, or DATETIME. The second parameter represents the format type. Possible values are EUR, INTERNAL, ISO, JIS, and USA. The following examples reflect all the possibilities.
Data type: Alphanumeric
GET_FORMAT(DATE, 'EUR') ⇨ '%d.%m.%Y'
GET_FORMAT(DATE, 'INTERNAL') ⇨ '%Y%m%d'
GET_FORMAT(DATE, 'ISO') ⇨ '%Y-%m-%d'
GET_FORMAT(DATE, 'JIS') ⇨ '%Y-%m-%d'
GET_FORMAT(DATE, 'USA') ⇨ '%m.%d.%Y'
GET_FORMAT(TIME, 'EUR') ⇨ '%H.%i.%s'
GET_FORMAT(TIME, 'INTERNAL') ⇨ '%H%i%s'
GET_FORMAT(TIME, 'ISO') ⇨ '%H:%i:%s'
GET_FORMAT(TIME, 'JIS') ⇨ '%H:%i:%s'
GET_FORMAT(TIME, 'USA') ⇨ '%h:%i:%s %p'
GET_FORMAT(DATETIME, 'EUR') ⇨ '%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME, 'INTERNAL') ⇨ '%Y%m%d%H%i%s'
GET_FORMAT(DATETIME, 'ISO') ⇨ '%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME, 'JIS') ⇨ '%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME, 'USA') ⇨ '%Y-%m-%d %H.%i.%s'
DATE_FORMAT('2005-01-01', GET_FORMAT(DATE, 'EUR'))
⇨ '01.01.2005'
DATE_FORMAT('2005-01-01', GET_FORMAT(DATE, 'ISO'))
⇨ '2005-01-01'
Description: This function returns the greatest value from a series of parameters.
Data type: Depends on parameters
GREATEST(100, 4, 80) ⇨ 100
GREATEST(DATE('2005-01-01'), DATE('2005-06-12'))
⇨ '2005-06-12'
Description: If the parameter is numeric, this function returns the hexadecimal representation of the parameter. If the parameter is alphanumeric, this function returns a two-digit code for each character.
Data type: Alphanumeric
HEX(11) ⇨ 'B'
HEX(16) ⇨ '10'
HEX(100) ⇨ '64'
HEX(1000) ⇨ '3E8'
HEX('3E8') ⇨ '334538'
HEX('ç') ⇨ 'E7'
Description: This function returns the number of the hour from a time or timestamp expression. The value of the result is always a whole number between 0 and 23 inclusive.
Data type: Numeric
HOUR('2005-01-01 12:13:14') ⇨ 12
HOUR('12:13:14') ⇨ 12
HOUR(CURTIME()) ⇨ 19
Description: If the value of the first parameter is true, the result of the function is equal to the value of the second parameter; otherwise, it is equal to the value of the third parameter. The specification
IF(E1, E2, E3)
in which E1, E2, and E3 are expressions, is equal to the following case expression:
CASE WHEN E1 = TRUE THEN E2 ELSE E3 END
Data type: Depends on the two last parameters
IF((5>8), 'Jim', 'John') ⇨ 'John' IF((SELECT COUNT(*) FROM PLAYERS) = (SELECT COUNT(*) FROM PENALTIES), TRUE, FALSE) ⇨ 0
Description: If the value of the first parameter is equal to the NULL value, the result of the function is equal to the value of the second parameter; otherwise, it is equal to the value of the first parameter. The specification
IFNULL(E1, E2)
in which E1, and E2 are expressions, is equal to the following case expression:
CASE E1 WHEN NULL THEN E2 ELSE E1 END
Data type: Depends on the parameters
IFNULL(NULL, 'John') ⇨ 'John'
IFNULL('John', 'Jim') ⇨ 'John'
Description: The value of the fourth parameter is placed on the part of the first parameter that starts with the position indicated with the second parameter, and that is a number of characters long (and that is the third parameter).
Data type: Alphanumeric
INSERT('abcdefgh',4,3,'zzz') ⇨ 'abczzzgh'
INSERT('abcdefgh',4,2,'zzz') ⇨ 'abczzzfgh'
INSERT('abcdefgh',4,0,'zzz') ⇨ 'abczzzdefgh'
INSERT('abcdefgh',4,-1,'zzz') ⇨ 'abczzz'
INSERT('abcdefgh',1,5,'zzz') ⇨ 'zzzfgh'
Description: This function returns the starting position of the second alphanumeric value within the first alphanumeric value. The INSTR function has the value zero if the second alphanumeric value does not appear within the first.
Data type: Numeric
INSTR('database','bas') ⇨ 5
INSTR('system','bas') ⇨ 0
Description: This function determines between which two values in a list the first parameter appears. After the first parameter, the values must be specified in ascending order.
Data type: Depends on the two last parameters
INTERVAL(3,0,1,2,3,4,5,6,7) ⇨ 4 INTERVAL(7,0,6,11,16,21) ⇨ 2
Description: The value of this function is equal to 1 if the first parameter is equal to the NULL value; otherwise, it is equal to 0. The specification
ISNULL(E1)
in which E1 is an expression, is equal to the following case expression:
CASE E1 WHEN NULL THEN 1 ELSE 0 END
Data type: Depends on parameters
ISNULL((SELECT LEAGUENO FROM PLAYERS WHERE PLAYERNO=27)) ⇨ 0 ISNULL((SELECT LEAGUENO FROM PLAYERS WHERE PLAYERNO=7)) ⇨ 1
Description: This function returns the last day of the month belonging to a date or timestamp expression.
Data type: Date
LAST_DAY('2004-02-01') ⇨ '2005-02-29'
LAST_DAY('2005-02-01') ⇨ '2005-02-28'
Description: This function converts all uppercase letters of the value of the parameter to lowercase letters.
Data type: Alphanumeric
LCASE('RICK') ⇨ 'rick'
Description: This function returns the smallest value from a series of parameters.
Data type: Depends on parameters
LEAST(100, 4, 80) ⇨ 4
LEAST(DATE('2005-01-01'), DATE('2005-06-12')) ⇨ 2005-01-01
Description: This function returns the left part of an alphanumeric value (the first parameter). The length of the part that is used is indicated with the second parameter.
Data type: Alphanumeric
LEFT('database', 4) ⇨ 'data'
LEFT('database', 0) ⇨ ''
LEFT('database', 10) ⇨ 'database'
LEFT('database', NULL) ⇨ ''
LENGTH(LEFT('database', 0)) ⇨ 0
LENGTH(LEFT('database', 10)) ⇨ 8
LENGTH(LEFT('database', NULL)) ⇨ 0
Description: This function returns the length in bytes of an alphanumeric value.
Data type: Numeric
LENGTH('database') ⇨ 8
LENGTH('data ') ⇨ 8
LENGTH(RTRIM('abcd ')) ⇨ 4
LENGTH('') ⇨ 0
LENGTH(NULL) ⇨ NULL
Description: This function returns the logarithm to the base value e of the parameter. See the LOG function.
Data type: Numeric
LN(50) ⇨ 3.9120230054281 LN(EXP(3)) ⇨ 3 LN(0) ⇨ NULL LN(1) ⇨ 0
Description: This function returns the system date and system time. If the function is used within a numeric expression, the result is numeric. The brackets may be left out. See the NOW and LOCALTIMESTAMP functions.
Data type: Timestamp or double
LOCALTIME() ⇨ '2005-02-20 12:26:52' LOCALTIME() + 0 ⇨ 20050220122652
Description: This function returns the system date and system time. If the function is used within a numeric expression, the result is numeric. The brackets may be left out. See the NOW and LOCALTIME functions.
Data type: Timestamp or double
LOCALTIMESTAMP() ⇨ '2005-02-20 12:26:52' LOCALTIMESTAMP() + 0 ⇨ 20050220122652
Description: This function returns the starting position of the first alphanumeric value within the second alphanumeric value. The LOCATE function has the value zero if the first alphanumeric value does not occur within the second. A third parameter may be included to indicate a position from which the search may be started.
Data type: Numeric
LOCATE('bas','database') ⇨ 5
LOCATE('bas','database',6) ⇨ 0
LOCATE('bas','system') ⇨ 0
Description: This function returns the logarithm to the base value e of the parameter.
Data type: Numeric
LOG(50) ⇨ 3.9120230054281 LOG(EXP(3)) ⇨ 3 LOG(0) ⇨ NULL LOG(1) ⇨ 0
Description: This function returns the logarithm of the second parameter where the first parameter forms the base value.
Data type: Numeric
LOG(10,1000) ⇨ 3 LOG(2,64) ⇨ 6
Description: This function returns the logarithm to the base value 2 of the parameter.
Data type: Numeric
LOG2(2) ⇨ 1 LOG2(64) ⇨ 6 LOG2(POWER(2,10) ⇨ 10
Description: This function returns the logarithm to the base value 10 of the parameter.
Data type: Numeric
LOG10(1000) ⇨ 3 LOG10(POWER(10,5)) ⇨ 5
Description: This function converts all uppercase letters of the value of the parameter to lowercase letters. See the LCASE function.
Data type: Alphanumeric
LOWER('RICK') ⇨ 'rick'
Description: The value of the first parameter is filled in the front (the left side) with the value of the third parameter just until the total length of the value is equal to that of the second parameter. If the maximum length is smaller than that of the first parameter, the first parameter is shortened on the left side.
Data type: Alphanumeric
LPAD('data', 16, 'base') ⇨ 'basebasebasedata'
LPAD('data', 6, 'base') ⇨ 'badata'
LPAD('data', 2, 'base') ⇨ 'da'
Description: This function removes all blanks that appear at the beginning of the parameter.
Data type: Alphanumeric
LTRIM(' database') ⇨ 'database'
Description: The second parameter represents a number of days, and those are added to the second parameter. This second parameter must be a numeric, date, or timestamp expression.
Data type: Date
MAKEDATE(2005, 1) ⇨ '2005-01-01'
MAKEDATE(2005, 10) ⇨ '2005-01-10'
MAKEDATE('2005-01-01', 1) ⇨ '2005-01-01'
MAKEDATE('2005-01-01 12:26:52', 1) ⇨ '2005-01-01'
Description: This function creates a time from a number of hours (the first parameter), a number of minutes (the second parameter), and a number of seconds (the third parameter). The number of minutes and the number of seconds must be between 0 and 59 inclusive; otherwise, the function returns the NULL value as result.
Data type: Time
MAKETIME(12,13,14) ⇨ '12:13:14' MAKETIME(12,90,14) ⇨ NULL MAKETIME(120,13,14) ⇨ '120:13:14'
Description: This function returns the number of microseconds from a time or timestamp expression. The value of the result is always a whole number between 0 and 999999 inclusive.
Data type: Numeric
MICROSECOND('2005-01-01 12:13:14.123456') ⇨ 123456
MICROSECOND('12:13:14.1') ⇨ 100000
Description: This function extracts part of the alphanumeric value of the first parameter. The second parameter identifies the start position, and the third parameter identifies the number of characters. See the SUBSTRING function.
Data type: Alphanumeric
MID('database',5) ⇨ 'base'
MID('database',10) ⇨ ''
MID('database',5,2) ⇨ 'ba'
MID('database',5,10) ⇨ 'base'
MID('database',-6) ⇨ 'tabase'
Description: This function returns the number of minutes from a time or timestamp expression. The value of the result is always a whole number between 0 and 59 inclusive.
Data type: Numeric
MINUTE(CURTIME()) ⇨ 52
MINUTE('12:40:33') ⇨ 40
Description: This function returns the remainder from the division of two parameters.
Data type: Numeric
MOD(15,4) ⇨ 3 MOD(15.4, 4.4) ⇨ 2.2
Description: This function returns the number of the month from a date or timestamp expression. The value of the result is always a whole number between 1 and 12 inclusive.
Data type: Numeric
MONTH('1988-07-29') ⇨ 7
Description: This function returns the name of the month from a date or timestamp expression.
Data type: Alphanumeric
MONTHNAME('1988-05-20') ⇨ 'May'
MONTHNAME('1988-06-20') ⇨ 'June'
Description: This function returns the system date and system time.
Data type: Timestamp
NOW() ⇨ '2005-12-20 12:26:52'
Description: If the value of the first parameter is not equal to that of the second parameter, the result of the function is equal to the NULL value; otherwise, it is equal to the first parameter. The specification
NULLIF(E1, E2)
in which E1 and E2 are two expressions, is equal to the following case expression:
CASE WHEN E1 = E2 THEN NULL ELSE E1 END
Data type: Depends on parameters
NULLIF(NULL, 'John') ⇨ NULL
NULLIF('John', 'Jim') ⇨ 'John'
NULLIF('John', 'John') ⇨ NULL
Description: This function returns the decimal of the first parameter. This parameter has an octal value.
Data type: Alphanumeric
OCT(8) ⇨ '10' OCT(64) ⇨ '100' OCT(100) ⇨ '144'
Description: This function returns the length in bytes of an octal value.
Data type: Numeric
OCTET_LENGTH('100') ⇨ 3
OCTET_LENGTH(OCT(64)) ⇨ 3
Description: This function returns the (ordinal) character set position of the first character of an alphanumeric expression.
Data type: Numeric
ORD('Database') ⇨ 68
ORD('database') ⇨ 100
ORD('') ⇨ 0
ORD(NULL) ⇨ NULL
Description: This function adds a number of months to a specific date. The date must have the format YYYYMM or YYMM. The format of the result is YYYYMM. Therefore, this function does not work with traditional dates.
Data type: Alphanumeric
PERIOD_ADD('200508', 2) ⇨ '200510'
PERIOD_ADD('200508', -2) ⇨ '200506'
PERIOD_ADD('200508', 12) ⇨ '200608'
Description: This function determines the number of months between two dates. Both dates must have the format YYYYMM or YYMM. Therefore, this function does not work with values with the date data type.
Data type: Numeric
PERIOD_DIFF('200508', '200510') ⇨ -2
PERIOD_DIFF('200508', '200506') ⇨ 2
PERIOD_DIFF('200508', '200608') ⇨ -12
Description: This function returns the well-known number pi.
Data type: Numeric
PI() ⇨ 3.141593 PI()*100000 ⇨ 314159.265359
Description: The value of the first expression is raised to a specific power. The second parameter indicates the power.
Data type: Numeric
POWER(4,3) ⇨ 64 POWER(2.5,3) ⇨ 15.625 POWER(4, 0.3) ⇨ 1.5157165665104 POWER(4, -2) ⇨ 0.0625
Description: This function returns the quarter from a date or timestamp expression. The value of the result is always a whole number between 1 and 4 inclusive.
Data type: Numeric
QUARTER('1988-07-29') ⇨ 3
QUARTER(CURDATE()) ⇨ 1
Description: This function converts a number in degrees to a value in radians.
Data type: Numeric
RADIANS(90) ⇨ 1.5707963267949 RADIANS(180) – PI() ⇨ 0 RADIANS(-360) ⇨ -6.2831853071796
Description: This function returns a random number (with a float data type) between 0.0 and 1.0. The parameter indicates the starting point for the calculation of the next random value. The result is the same when this function is called repeatedly with the same parameter value. If no parameter has been specified, the next random value is calculated.
Data type: Numeric
RAND() ⇨ 0.42908766346899 RAND(5) ⇨ 0.40613597483014 CAST(RAND() * 10000 AS UNSIGNED INTEGER) ⇨ 8057
Description: This function repeats an alphanumeric value (the first parameter) a specified number of times. The second parameter indicates the number of times.
Data type: Alphanumeric
REPEAT('bla',4) ⇨ 'blablablabla'
REPEAT('X',10) ⇨ 'XXXXXXXXXX'
Description: This function replaces parts of the value of an alphanumeric expression with another value.
Data type: Alphanumeric
REPLACE('database','a','e') ⇨ 'detebese'
REPLACE('database','ba','warehou') ⇨ 'datawarehouse'
REPLACE('data base',' ','') ⇨ 'database'
Description: This function reverses the characters in an alphanumeric value.
Data type: Alphanumeric
REVERSE('database') ⇨ 'esabatad'
Description: This function returns the right part of an alphanumeric value (the first parameter). The length of the part that is used is indicated with the second parameter.
Data type: Alphanumeric
RIGHT('database', 4) ⇨ 'base'
RIGHT('database', 0) ⇨ ''
RIGHT('database', 10) ⇨ 'database'
RIGHT('database', NULL) ⇨ ''
LENGTH(RIGHT('database', 0)) ⇨ 0
LENGTH(RIGHT('database', 10)) ⇨ 8
LENGTH(RIGHT('database', NULL)) ⇨ 0
Description: This function rounds numbers to a specified number of decimal places. If the second parameter has not been specified, it is equal to the specification of 0.
Data type: Numeric
ROUND(123.456,2) ⇨ 123.46 ROUND(123.456,1) ⇨ 123.5 ROUND(123.456,0) ⇨ 123 ROUND(123.456,-1) ⇨ 120 ROUND(123.456,-2) ⇨ 100 ROUND(123.456) ⇨ 123
Description: The value of the first parameter is filled in the front (the right side) with the value of the third parameter just until the total length of the value is equal to that of the second parameter. If the maximum length is smaller than that of the first parameter, the first parameter is shortened on the right side.
Data type: Alphanumeric
RPAD('data', 16, 'base') ⇨ 'databasebasebase'
RPAD('data', 6, 'base') ⇨ 'databa'
RPAD('data', 2, 'base') ⇨ 'da'
Description: This function removes all blanks from the end of the value of the parameter.
Data type: Alphanumeric
RTRIM('database ') ⇨ 'database'
CONCAT(RTRIM('data '), 'base') ⇨ 'database'
Description: This function returns the number of seconds from a time or timestamp expression. The value of the result is always a whole number between 0 and 59 inclusive.
Data type: Numeric
SECOND(CURTIME()) ⇨ 6
SECOND('12:40:33') ⇨ 33
Description: This function transforms a number of seconds in a time.
Data type: Time
SEC_TO_TIME(1) ⇨ '00:00:01' SEC_TO_TIME(1000) ⇨ '00:16:40' SEC_TO_TIME((24*60*60)-1) ⇨ '23:59:59' SEC_TO_TIME(24*60*60*2) ⇨ '48:00:00'
Description: This function returns the name of the SQL user.
Data type: Alphanumeric
SESSION_USER() ⇨ 'root@localhost'
Description: This function returns the character of a numeric value.
Data type: Numeric
SIGN(50) ⇨ 1 SIGN(0) ⇨ 0 SIGN(-50) ⇨ –1
Description: This function returns, in radians, the sine value of any angle size.
Data type: Numeric
SIN(0) ⇨ 0 SIN(PI()/2) ⇨ 1 SIN(PI()) ⇨ 0
Description: This function returns the SOUNDEX code of the alphanumeric parameter. A SOUNDEX code consists of four characters. Alphanumeric values that sound roughly the same are converted to identical SOUNDEX codes. The SOUNDEX code is specified according to the following rules:
All blanks at the beginning of the parameter are removed.
All the following letters are removed from the parameter, provided that they do not appear on the first position:
a e h i o u w y.The following values are assigned to the remaining letters:
b f p v = 1 c g j k q s x z = 2 d t = 3 l = 4 m n = 5 r = 6
If two linked letters have the same value, the second is removed.
The code is broken after the fourth character.
If the remaining code consists of less than four characters, it is filled with zeroes.
Characters appearing behind a blank are skipped.
If the value of the parameter does not begin with a letter, the result is equal to
0000.
Data type: Alphanumeric
SOUNDEX('Smith') ⇨ 'S530'
SOUNDEX('Smythe') ⇨ 'S530'
SOUNDEX('Bill') ⇨ 'B400'
SOUNDEX(' Bill') ⇨ 'B400'
SOUNDEX('Billy') ⇨ 'B400'
Description: This function generates a row with blanks. The number of blanks is equal to the value of the numeric parameter.
Data type: Alphanumeric
SPACE(1) ⇨ ' ' SPACE(5) ⇨ ' ' LENGTH(SPACE(8)) ⇨ 8
Description: This function returns the square root of the value of the parameter.
Data type: Numeric
SQRT(225) ⇨ 15 SQRT(200) ⇨ 14.14 SQRT(-5) ⇨ NULL
Description: This function compares the values of two alphanumeric expressions. The result is 0 if the values of the parameters are equal, -1 if the value of the first parameter is smaller, and 1 if the value of the right one is smaller.
Data type: Numeric
STRCMP(1,1) ⇨ 0 STRCMP(1,2) ⇨ -1 STRCMP(2,1) ⇨ 1
Description: This function is the opposite of the DATE_FORMAT function. A certain alphanumeric value is converted to a date or timestamp value through a number of format strings. If the format strings do not fit in the first parameter, the function returns a NULL value as result.
Data type: Date or timestamp
STR_TO_DATE('2005 Sun Oct 1st', '%Y %a %b %D')⇨ '2005-10-01'
STR_TO_DATE('2005/11/10', '%Y/%c/%d') ⇨ '2005-11-10'
Description: This function subtracts an interval (the second parameter) from a date or timestamp expression (the first parameter). See Section 5.13.3, in Chapter 5, for the specification of intervals. If the second parameter is not an interval but a numeric number, SQL assumes that this value represents a number of days.
Data type: Date or timestamp
SUBDATE('2004-01-01', INTERVAL 5 MONTH) ⇨ '2003-08-01'
SUBDATE('2004-01-01 12:00:00', INTERVAL 5 DAY)
⇨ '2003-12-27 12:00:00'
SUBDATE('2004-01-01', 5) ⇨ '2003-12-27'
Description: This function extracts part of the alphanumeric value of the first parameter. The second parameter identifies the starting position, and the third one identifies its number of characters. If the third parameter has not been specified, up to the last character is included.
Data type: Alphanumeric
SUBSTRING('database',5) ⇨ 'base'
SUBSTRING('database',10) ⇨ ''
SUBSTRING('database',5,2) ⇨ 'ba'
SUBSTRING('database',5,10) ⇨ 'base'
SUBSTRING('database',-6) ⇨ 'tabase'
Description: This function extracts part of the alphanumeric value of the first parameter. The second parameter identifies the starting position, and the third one identifies its number of characters. If the third parameter has not been specified, up to the last character is included.
Data type: Alphanumeric
SUBSTRING('database' FROM 5) ⇨ 'base'
SUBSTRING('database' FROM 10) ⇨ ''
SUBSTRING('database' FROM 5 FOR 2) ⇨ 'ba'
SUBSTRING('database' FROM 5 FOR 10) ⇨ 'base'
SUBSTRING('database' FROM -6) ⇨ 'tabase'
Description: This function looks for the nth appearance of an alphanumeric value in the value of the first parameter. The second parameter shows which value must be looked for, and the third parameter returns the number n. If the third parameter is positive, the function looks for the nth appearance from the left side and returns everything that is found left from that appearance. If the third parameter is negative, the function looks for the nth appearance from the right and returns everything that is found right from that appearance.
Data type: Alphanumeric
SUBSTRING_INDEX('database', 'a', 3) ⇨ 'datab'
SUBSTRING_INDEX('database', 'a', -3) ⇨ 'tabase'
SUBSTRING_INDEX('database', 'data', 1) ⇨ ''
SUBSTRING_INDEX('database', 'data', -1) ⇨ 'base'
Description: This function subtracts two time expressions and returns a new time.
Data type: Time
SUBTIME('12:59:00', '0:59:00') ⇨ '12:00:00'
SUBTIME('12:00:00', '0:00:00.001') ⇨ '11:59:59.999000'
SUBTIME('100:00:00', '900:00:00') ⇨ '-800:00:00'
Description: This function returns the system date and system time. If the function is used within a numeric expression, the result is numeric. See the LOCALTIME and LOCALTIMESTAMP functions.
Data type: Timestamp or numeric
SYSDATE() ⇨ '2005-02-20 12:26:52' SYSDATE() + 0 ⇨ 20050220122652
Description: This function returns the name of the SQL user.
Data type: Alphanumeric
SYSTEM_USER() ⇨ 'root@localhost'
Description: This function returns, in radians, the tangent value of any angle size.
Data type: Numeric
TAN(0) ⇨ 0 TAN(PI()) ⇨ 0 TAN(PI()/4 ⇨ 1 TAN(1) ⇨ 1.5574077246549
Description: This function returns the time part of a time or timestamp expression.
Data type: Time
TIME('2005-12-08 12:00:00') ⇨ '12:00:00'
TIME('12:13') ⇨ '12:13:00'
Description: This function returns the amount of time that has elapsed between two time expressions.
Data type: Time
TIMEDIFF('12:00:01','12:00:00') ⇨ '00:00:01'
TIMEDIFF('12:00:00','12:00:01') ⇨ '-00:00:01'
TIMEDIFF('23:01:01','22:00:59') ⇨ '01:00:02'
Description: This function transforms a time, date, or timestamp expression (the first parameter) to an alphanumeric value. The second parameter indicates the format of that alphanumeric value, and several special format strings can be used here; see the following table. This function looks like the DATE_FORMAT function; however, all time-related format strings may be used now.
FORMAT STRING | EXPLANATION |
|---|---|
| Six-digit numeric code for the number of microseconds ( |
| Two-digit numeric code for the hour ( |
| Two-digit numeric code for the hour ( |
| Two-digit numeric code for the hour ( |
| Two-digit numeric code for the number of minutes ( |
| One- or two-digit numeric code for the hour ( |
| One- or two-digit numeric code for the hour ( |
| Indication of |
| Indication of the time (in 12 hours) with the format |
| Two-digit numeric code for the number of seconds ( |
| Two-digit numeric code for the number of seconds ( |
| Indication of the time (in 24 hours) in the format hh:mm:ss followed by |
| Returns the percentage sign |
Data type: Alphanumeric
TIME_FORMAT('11:12:13','%h') ⇨ '11'
TIME_FORMAT('11:12:13','%f') ⇨ '000000'
TIME_FORMAT('12:00:00', 'It is now %h o''clock')
⇨ 'It is now 12 o'clock'
Description: This function transforms the first parameter into a timestamp value. If a second parameter is specified, it should be a time expression, and that is added to the value of the first parameter.
Data type: Timestamp
TIMESTAMP('2005-12-08') ⇨ '2005-12-08 00:00:00'
TIMESTAMP('2005-12-08 12:00:00') ⇨ '2005-12-08 12:00:00'
TIMESTAMP('2005-12-08 12:00:00', '11:12:13')
⇨ '2005-12-08 23:12:13'
TIMESTAMP('2005-12-08 12:00:00', '-11:12:00')
⇨ '2005-12-08 00:48:00'
TIMESTAMP('2005-12-08 12:00:00', '-48:00')
⇨ '2005-12-06 12:00:00'
Description: This function adds a certain interval to a date or timestamp expression. The first parameter indicates the unit of the interval, such as days, months, or years, and the second parameter indicates the number of days or months. The third parameter is the expression to which the interval is added. Supported interval units are YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and FRAC_SECOND.
Data type: Date or timestamp
TIMESTAMPADD(DAY, 2, '2005-12-08') ⇨ '2005-12-10' TIMESTAMPADD(MONTH, 2, '2005-12-08') ⇨ '2006-02-08' TIMESTAMPADD(YEAR, -2, '2005-12-08') ⇨ '2003-12-08' TIMESTAMPADD(MINUTE, 3, '2005-12-08 12:00:00') ⇨ '2005-12-08 12:03:00' TIMESTAMPADD(FRAC_SECOND, 3, '2005-12-08 12:00:00') ⇨ '2005-12-08 12:00:00.000003'
Description: This function calculates the time between two date or timestamp expressions. The first parameter indicates the unit of the interval, such as days, months, or years; the second and third parameters form the two expressions. Supported interval units are YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and FRAC_SECOND.
TIMESTAMPDIFF(DAY, '2005-12-04', '2005-12-08') ⇨ 4 TIMESTAMPDIFF(DAY, '2005-12-08', '2005-12-04') ⇨ -4 TIMESTAMPDIFF(YEAR, '1960-12-08', NOW()) ⇨ 45 TIMESTAMPDIFF(MINUTE, '2005-12-08 12:00:00', '2005-12-08 12:03:00') ⇨ 3 TIMESTAMPDIFF(FRAC_SECOND, '2005-12-08', '2005-12-08 12:00:00.000003') ⇨ 43200000003
Description: This function transforms a time into a number of seconds.
Data type: Numeric
TIME_TO_SEC('00:00:01') ⇨ 1
TIME_TO_SEC('00:16:40') ⇨ 1000
TIME_TO_SEC('23:59:59') ⇨ 83399
TIME_TO_SEC('48:00:00') ⇨ 172800
Description: This function determines how many days have elapsed between the specified date (the parameter) and the year 0.
Data type: Numeric
TO_DAYS('2005-12-08') ⇨ 732653
Description: This function removes all blanks from the start and from the end of an alphanumeric value (the parameter). Blanks in the middle are not removed.
Data type: Alphanumeric
TRIM('database ') ⇨ 'database'
TRIM(' da ta ') ⇨ 'da ta'
Description: This function truncates numbers to a specified number of decimal places.
Data type: Numeric
TRUNCATE(123.567, -1) ⇨ 120 TRUNCATE(123.567, 1) ⇨ 123.5 TRUNCATE(123.567, 5) ⇨ 123.56700
Description: This function converts all lowercase letters of the value of the parameter to uppercase letters. See the UPPER function.
Data type: Alphanumeric
UCASE('Database') ⇨ 'DATABASE'
Description: This function returns the hexadecimal representation of the parameter. Each pair of characters is converted to the corresponding character.
Data type: Alphanumeric
UNHEX('334538') ⇨ '3E8'
UNHEX('E7') ⇨ 'ç'
UNHEX(HEX('SQL')) ⇨ 'SQL'
Description: This function converts all lowercase letters of the value of the parameter to uppercase letters.
Data type: Alphanumeric
SQL, UPPER('Database') ⇨ 'DATABASE'
Description: This function returns the name of the SQL user.
Data type: Alphanumeric
USER() ⇨ 'root@localhost'
Description: This function returns the actual UTC date. UTC stands for Coordinated Universal Time, or Zulu time, or Greenwich mean time (GMT). If the function is part of a numeric expression, the result of the function will also be numeric.
Data type: Date or numeric
UTC_DATE() ⇨ '2005-01-01' UTC_DATE() + 0 ⇨ 20050101
Description: This function returns the actual UTC date; see the UTC_DATE function. If the function is part of a numeric expression, the result of the function will also be numeric.
Data type: Date or numeric
UTC_TIME() ⇨ '2005-01-01' HOUR(TIMEDIFF(UTC_TIME(), TIME(NOW()))) ⇨ 1
Description: This function returns the actual UTC date and time; see the UTC_DATE function. If the function is part of a numeric expression, the result of the function will also be numeric.
Data type: Date or numeric
UTC_TIMESTAMP() ⇨ '2005-01-01 13:56:12'
Description: This function generates an 18-byte wide unique code. The abbreviation UUID stands for Universal Unique Identifier. The first three parts of this code are derived from the system time. The fourth part must make sure that the codes are unique, in case duplicate values can arise because of time zones. The fifth part identifies the server in a certain way. Generating unique values is not guaranteed, but it is most likely that it happens.
Data type: Alphanumeric
UUID() ⇨ '2bf2aaec-bc90-1028-b6bf-cc62846e9cc5' UUID() ⇨ '390341e3-bc90-1028-b6bf-cc62846e9cc5'
Description: This function returns an identification of the version number of MySQL.
Data type: Alphanumeric
VERSION() ⇨ '5.0.7-beta-nt' VERSION() ⇨ '5.0.3-alpha-log'
Description: This function returns the week from a date or timestamp expression. The value of the result is always a whole number between 1 and 53 inclusive.
Data type: Numeric
WEEK('1988-07-29') ⇨ 30
WEEK('1997-01-01') ⇨ 1
WEEK('2000-12-31') ⇨ 53
WEEK(CURDATE()) ⇨ 7
Description: This function returns the number of the day in the week. The result is a number between 0 (Monday) and 6 (Sunday).
Data type: Numeric
WEEKDAY('2005-01-01') ⇨ 5
Description: This function returns the week number belonging to a certain date expression. The result is a number between 1 and 53.
Data type: Numeric
WEEKOFYEAR('2005-01-01') ⇨ 53
WEEKOFYEAR('2005-01-03') ⇨ 1
Description: This function returns the number of the year from a date or timestamp expression. The result is always a number greater than 1.
Data type: Numeric
YEAR(NOW()) ⇨ 1998
Description: If only one parameter is specified, this function returns the year followed by the week number in the format YYYYWW from a date or timestamp expression. The week number goes from 01 to 53 inclusive. It is assumed that a week starts on Sunday. If a second parameter is specified, it must be the same code as the one used in the WEEK function.
Data type: Numeric
YEARWEEK('2005-12-03') ⇨ 200548
YEARWEEK('2005-12-03',0) ⇨ 200548
YEARWEEK('2005-01-02',0) ⇨ 200501
YEARWEEK('2005-01-02',1) ⇨ 200453