IST366:MySQL String and Date functions.

String Functions

Function	Description
ASCII(str)	Returns the ASCII code value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL
ORD(str)	If the leftmost character of the string str is a multi-byte character, returns the code of multi-byte character by returning the ASCII code value of the character in the format of: ((first byte ASCII code)256+(second byte ASCII code))[256+third byte ASCII code...]. If the leftmost character is not a multi-byte character, returns the same value as the like `ASCII()` function does.
CONV(N,from_base,to_base)	Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. `CONV` works with 64-bit precision.
BIN(N)	Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to `CONV(N,10,2)`. Returns NULL if N is NULL.
OCT(N)	Returns a string representation of the octal value of N, where N is a longlong number. This is equivalent to `CONV(N,10,8)`. Returns NULL if N is NULL.
HEX(N_or_S)	If N_OR_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to `CONV(N,10,16)`. If N_OR_S is a string, returns a hexadecimal string of N_OR_S where each character in N_OR_S is converted to 2 hexadecimal digits. This is the invers of the 0xff strings.
CHAR(N,...)	interprets the arguments as integers and returns a string consisting of the characters given by the ASCII code values of those integers. NULL values are skipped.
CONCAT(str1,str2,...)	Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have more than 2 arguments. A numeric argument is converted to the equivalent string form.
CONCAT_WS(separator, str1, str2,...)	stands for CONCAT With Separator and is a special form of `CONCAT()`. The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULLs and empty strings, after the separator argument. The separator will be added between the strings to be concatenated.
LENGTH(str) OCTET_LENGTH(str) CHAR_LENGTH(str) CHARACTER_LENGTH(str)	Returns the length of the string str. Note that for `CHAR_LENGTH()`, multi-byte characters are only counted once.
BIT_LENGTH(str)	Returns the length of the string str in bits.
LOCATE(substr,str) POSITION(substr IN str)	Returns the position of the first occurrence of substring substr in string str. Returns 0 if substr is not in str. This function is multi-byte safe. In MySQL 3.23 this function is case insensitive, while in 4.0 it's only case insensitive if either argument is a binary string.
LOCATE(substr,str,pos)	Returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.
INSTR(str,substr)	Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of `LOCATE()`, except that the arguments are swapped.
LPAD(str,len,padstr)	Returns the string str, left-padded with the string padstr until str is len characters long. If str is longer than len, then it will be shortened to len characters.
RPAD(str,len,padstr)	Returns the string str, right-padded with the string padstr until str is len characters long. If str is longer than len, then it will be shortened to len characters.
LEFT(str,len)	Returns the leftmost len characters from the string str. This function is multi-byte safe.
RIGHT(str,len)	Returns the rightmost len characters from the string str.
SUBSTRING(str,pos,len) SUBSTRING(str FROM pos FOR len) MID(str,pos,len)	Returns a substring len characters long from string str, starting at position pos. The variant form that uses FROM is ANSI SQL92 syntax.
SUBSTRING(str,pos) SUBSTRING(str FROM pos)	Returns a substring from string str starting at position pos.
SUBSTRING_INDEX(str,delim,count)	Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.
LTRIM(str)	Returns the string str with leading space characters removed.
RTRIM(str)	Returns the string str with trailing space characters removed.
TRIM([[BOTH \| LEADING \| TRAILING] [remstr] FROM] str)	Returns the string str with all remstr prefixes and/or suffixes removed. If none of the specifiers `BOTH`, `LEADING` or `TRAILING` are given, `BOTH` is assumed. If `remstr` is not specified, spaces are removed.
SOUNDEX(str)	Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is 4 characters long, but the `SOUNDEX()` function returns an arbitrarily long string. You can use `SUBSTRING()` on the result to get a standard soundex string. All non-alphanumeric characters are ignored in the given string. All international alpha characters outside the A-Z range are treated as vowels.
SPACE(N)	Returns a string consisting of N space characters.
REPLACE(str,from_str,to_str)	Returns the string str with all all occurrences of the string from_str replaced by the string to_str.
REPEAT(str,count)	Returns a string consisting of the string str repeated count times. If count <= 0, returns an empty string. Returns NULL if str or count are NULL.
REVERSE(str)	Returns the string str with the order of the characters reversed.
INSERT(str,pos,len,newstr)	Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr.
ELT(N,str1,str2,str3,...)	Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments.
FIELD(str,str1,str2,str3,...)	Returns the index of str in the str1, str2, str3, ... list. Returns 0 if str is not found.
FIND_IN_SET(str,strlist)	Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by `,' characters. If the first argument is a constant string and the second is a column of type `SET`, the `FIND_IN_SET()` function is optimised to use bit arithmetic! Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a `,'.
MAKE_SET(bits,str1,str2,...)	Returns a set (a string containing substrings separated by `,' characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, etc. NULL strings in str1, str2, ... are not appended to the result.
LCASE(str) LOWER(str)	Returns the string str with all characters changed to lowercase according to the current character set mapping (the default is ISO-8859-1 Latin1).
UCASE(str) UPPER(str)	Returns the string str with all characters changed to uppercase according to the current character set mapping (the default is ISO-8859-1 Latin1).
LOAD_FILE(file_name)	Reads the file and returns the file contents as a string. The file must be on the server, you must specify the full pathname to the file, and you must have the `file` privilege. The file must be readable by all and be smaller than `max_allowed_packet`. If the file doesn't exist or can't be read due to one of the above reasons, the function returns NULL. Example: UPDATE table_name SET blob_column=LOAD_FILE("/tmp/picture") WHERE id=1;

Date Functions

Function Description

DAYOFWEEK(date) Returns the weekday index for date (1 = Sunday, 2 = Monday, ... 7 = Saturday). These index values correspond to the ODBC standard.

WEEKDAY(date) Returns the weekday index for date (0 = Monday, 1 = Tuesday, ... 6 = Sunday).

DAYOFMONTH(date) Returns the day of the month for date, in the range 1 to 31.

DAYOFYEAR(date) Returns the day of the year for date, in the range 1 to 366.

MONTH(date) Returns the month for date, in the range 1 to 12.

DAYNAME(date) Returns the name of the weekday for date.

MONTHNAME(date) Returns the name of the month for date.

QUARTER(date) Returns the quarter of the year for date, in the range 1 to 4.

WEEK(date)
WEEK(date,first) With a single argument, returns the week for date, in the range 0 to 53 (yes, there may be the beginnings of a week 53), for locations where Sunday is the first day of the week. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday. The week starts on Sunday if the second argument is 0, on Monday if the second argument is 1.

YEAR(date) Returns the year for date, in the range 1000 to 9999.

YEARWEEK(date)
YEARWEEK(date,first) Returns year and week for a date. The second arguments works exactly like the second argument to WEEK(). Note that the year may be different from the year in the date argument for the first and the last week of the year.

HOUR(time) Returns the hour for time, in the range 0 to 23.

MINUTE(time) Returns the minute for time, in the range 0 to 59.

SECOND(time) Returns the second for time, in the range 0 to 59.

PERIOD_ADD(P,N) Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value.

PERIOD_DIFF(P1,P2) Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.

DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)

These functions perform date arithmetic. They are new for MySQL Version 3.22. ADDDATE() and SUBDATE() are synonyms for DATE_ADD() and DATE_SUB(). In MySQL Version 3.23, you can use + and - instead of DATE_ADD() and DATE_SUB() if the expression on the right side is a date or datetime column. (See example) date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a `-' for negative intervals. type is a keyword indicating how the expression should be interpreted. The related function EXTRACT(type FROM date) returns the 'type' interval from the date. The following table shows how the type and expr arguments are related:

type value	Expected expr format
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
MONTH	MONTHS
YEAR	YEARS
MINUTE_SECOND	"MINUTES:SECONDS"
HOUR_MINUTE	"HOURS:MINUTES"
DAY_HOUR	"DAYS HOURS"
YEAR_MONTH	"YEARS-MONTHS"
HOUR_SECOND	"HOURS:MINUTES:SECONDS"
DAY_MINUTE	"DAYS HOURS:MINUTES"
DAY_SECOND	"DAYS HOURS:MINUTES:SECONDS"

MySQL allows any punctuation delimiter in the expr format. Those shown in the table are the suggested delimiters. If the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts), the result is a DATE value. Otherwise the result is a DATETIME value.
Example:

SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
SELECT INTERVAL 1 DAY + "1997-12-31";
SELECT "1998-01-01" - INTERVAL 1 SECOND;
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND);
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 DAY);
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND);
SELECT DATE_SUB("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND);
SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR);
SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);

EXTRACT(type FROM date) The EXTRACT() function uses the same kinds of interval type specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.

TO_DAYS(date) Given a date date, returns a daynumber (the number of days since year 0). TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it doesn't take into account the days that were lost when the calendar was changed.

FROM_DAYS(N) Given a daynumber N, returns a DATE value.

DATE_FORMAT(date,format)

Formats the date value according to the format string. The following specifiers may be used in the format string:

Specifier	Description
%M	Month name (January..December)
%W	Weekday name (Sunday..Saturday)
%D	Day of the month with English suffix (1st, 2nd, 3rd, etc.)
%Y	Year, numeric, 4 digits
%y	Year, numeric, 2 digits
%X	Year for the week where Sunday is the first day of the week, numeric, 4 digits, used with '%V'
%x	Year for the week, where Monday is the first day of the week, numeric, 4 digits, used with '%v'
%a	Abbreviated weekday name (Sun..Sat)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%m	Month, numeric (01..12)
%c	Month, numeric (1..12)
%b	Abbreviated month name (Jan..Dec)
%j	Day of year (001..366)
%H	Hour (00..23)
%k	Hour (0..23)
%h	Hour (01..12)
%I	Hour (01..12)
%l	Hour (1..12)
%i	Minutes, numeric (00..59)
%r	Time, 12-hour (hh:mm:ss [AP]M)
%T	Time, 24-hour (hh:mm:ss)
%S	Seconds (00..59)
%s	Seconds (00..59)
%p	AM or PM
%w	Day of the week (0=Sunday..6=Saturday)
%U	Week (0..53), where Sunday is the first day of the week
%u	Week (0..53), where Monday is the first day of the week
%V	Week (1..53), where Sunday is the first day of the week. Used with '%X'
%v	Week (1..53), where Monday is the first day of the week. Used with '%x'
%%	A literal `%'.

All other characters are just copied to the result without interpretation.
Example:

select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
select DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j');

TIME_FORMAT(time,format) This is used like the DATE_FORMAT() function above, but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0.

CURDATE()
CURRENT_DATE Returns today's date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

CURTIME()
CURRENT_TIME Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.

NOW()
SYSDATE()
CURRENT_TIMESTAMP Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

SEC_TO_TIME(seconds) Returns the seconds argument, converted to hours, minutes, and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.

TIME_TO_SEC(time) Returns the time argument, converted to seconds.

Control Flow Functions

Function	Description
IFNULL(expr1, expr2)	If expr1 is not NULL, `IFNULL()` returns expr1, else it returns expr2. `IFNULL()` returns a numeric or string value, depending on the context in which it is used.
IF(expr, res1, res2)	If expr is TRUE (expr <> 0 and expr <> NULL) then `IF()` returns res1, else it returns res2. `IF()` returns a numeric or string value, depending on the context in which it is used
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END	returns the result where value=compare-value
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END	returns the result for the first condition, which is true. If there was no matching result value, then the result after ELSE is returned. If there is no ELSE part then NULL is returned

Mathematecal Functions

Please read about Math functions here.

Other Functions

Function	Description
DATABASE()	Returns the current database name
USER() SYSTEM_USER() SESSION_USER()	Returns the current MySQL user name
PASSWORD(str)	Calculates a password string from the plaintext password str. This is the function that is used for encrypting MySQL passwords for storage in the `Password` column of the user grant table. `PASSWORD()` encryption is non-reversible. `PASSWORD()` does not perform password encryption in the same way that Unix passwords are encrypted
ENCRYPT(str[, salt])	Encrypt str using the Unix `crypt()` system call. The salt argument should be a string with two characters. (As of MySQL Version 3.22.16, salt may be longer than two characters.) If `crypt()` is not available on your system, `ENCRYPT()` always returns NULL. `ENCRYPT()` ignores all but the first 8 characters of str, at least on some systems. This will be determined by the behavior of the underlying `crypt()` system call.
LAST_INSERT_ID()	Returns the last automatically generated value that was inserted into an `AUTO_INCREMENT` column. The last ID that was generated is maintained in the server on a per-connection basis. It will not be changed by another client. If you insert many rows at the same time with an insert statement, `LAST_INSERT_ID()` returns the value for the first inserted row.
FORMAT(X, D)	Formats the number X to a format like '#,###,###.##', rounded to D decimals. If D is 0, the result will have no decimal point or fractional part.
VERSION()	Returns a string indicating the MySQL server version.
CONNECTION_ID()	Returns the connection id (thread_id) for the connection. Every connection has its own unique id.

You can find more of those functions here.

For additional information see MySQL String Functions and Date functions documentation.